一個雞大巴粗JDBC代碼案例

一個JDBC案例

一、定義

JDBC全稱為：Java DataBase Connectivity（java數(shù)據(jù)庫連接）。是SUN公司為了簡化、統(tǒng)一對數(shù)據(jù)庫的操作，定義了一套Java操作數(shù)據(jù)庫的規(guī)范，稱之為JDBC，即Java數(shù)據(jù)庫編程接口，是一組標(biāo)準(zhǔn)的Java語言中的接口和類，使用這些接口和類，Java客戶端程序可以訪問各種不同類型的數(shù)據(jù)庫。比如建立數(shù)據(jù)庫連接、執(zhí)行SQL語句進(jìn)行數(shù)據(jù)的存取操作。

二、為什么要使用JDBC?

  1. 數(shù)據(jù)庫產(chǎn)品眾多,保守估計有100多種數(shù)據(jù),這樣造成我們應(yīng)用程序連接訪問數(shù)據(jù)庫缺乏統(tǒng)一的接口
  2. 應(yīng)用程序與數(shù)據(jù)庫之間兼容性太差

三、使用JDBC的好處

  1. 統(tǒng)一應(yīng)用平臺，實現(xiàn)異構(gòu)數(shù)據(jù)庫系統(tǒng)信息互訪
  2. 減少應(yīng)用程序與數(shù)據(jù)庫編碼工作
  3. 依靠 OOP技術(shù),提高代碼的復(fù)用性

?。DBC制定了統(tǒng)一訪問各類關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)接口，為各個數(shù)據(jù)庫廠商提供了標(biāo)準(zhǔn)接口的實現(xiàn)??！


jdbc交互.png

案例

實體類-User

package com.yeungkim.entity;

import java.io.Serializable;
import java.sql.Timestamp;

//實現(xiàn)序列化接口
public class User implements Serializable {
    private String username ;
    private String password;
    private Integer uid;
    private String phone;
    private String email;
    private Timestamp createDate;
    private Integer isDel;

    public User(String username, String password, int uid, String phone, String email, Timestamp createDate, int isDel) {
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Timestamp getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Timestamp createDate) {
        this.createDate = createDate;
    }

    public Integer getIsDel() {
        return isDel;
    }

    public void setIsDel(Integer isDel) {
        this.isDel = isDel;
    }

    public User() {
    }
}

實體類-Detail

package com.yeungkim.entity;

import java.io.Serializable;
//實現(xiàn)序列化接口
public class Detail implements Serializable {
    //使用包裝類數(shù)據(jù)類型
    private Integer detailId;
    private String address;
    private Integer uid;
    private Integer isDel;
//getter setter 封裝私有化
    public Integer getDetailId() {
        return detailId;
    }

    public void setDetailId(Integer detailId) {
        this.detailId = detailId;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Integer getIsDel() {
        return isDel;
    }

    public void setIsDel(Integer isDel) {
        this.isDel = isDel;
    }
//無參構(gòu)造方法
    public Detail() {
    }
}

Dao-UseDao

package Dao;

import com.yeungkim.entity.User;

import java.util.List;

public interface UserDao {
    //查詢所有用戶
    List<User> selectAll();

    //根據(jù)用戶名查詢用戶信息
    User selectUserByName(String username);

    //添加用戶信息

    int insert(User user);

    //根據(jù)id修改用戶信息，因為id已經(jīng)在user

    int updateById(User user);


}

Dao-DetailDao

package Dao;

import com.yeungkim.entity.Detail;
import com.yeungkim.entity.User;

public interface DetailsDao {
    int insertDetail(Detail detail, User user);

}

userDaoImpl

package Dao.impl;

import Dao.UserDao;
import com.yeungkim.entity.User;
import com.yeungkim.utils.Dbutils;
import jdk.nashorn.internal.ir.CallNode;

import javax.jws.soap.SOAPBinding;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public List<User> selectAll() {
        List<User>users=null;
        // uid, username, password, phone, email, create_date, is_del
        String sql="select * from user where is_del=0";

        //配置文件，變量
        try {
            Connection connection = Dbutils.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet!=null){
                users=new ArrayList<>();
                while (resultSet.next()){
                    //一次for循環(huán)就是一個對象
                    int uid =resultSet.getInt("uid");
                    String username=resultSet.getString("username");
                    String password=resultSet.getString("password");
                    String phone=resultSet.getString("phone");
                    String email=resultSet.getString("email");
                    Timestamp createDate=resultSet.getTimestamp("create_date");
                    int isDel=resultSet.getInt("is_del");
                    //將數(shù)據(jù)庫封裝到對象，添加到集合中
                    users.add(new User(username,password,uid,phone,email,createDate,isDel));

                }

            }
            Dbutils.close(connection,preparedStatement,resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //sql注入的問題
        //


        return users;
    }

    @Override
    public User selectUserByName(String username) {
        Connection connection=null;
        ResultSet resultSet=null;
        PreparedStatement preparedStatement=null;
        User user=null;

            //建立鏈接
        try {
            connection = Dbutils.getConnection();
            //sql語句
            String sql="select uid, username, password, phone, email,is_del from user where username=?";
            //創(chuàng)建ps對象
            preparedStatement = connection.prepareStatement(sql);
            //設(shè)置參數(shù)
            preparedStatement.setString(1,username);
            //執(zhí)行查詢返回result 對象
             resultSet = preparedStatement.executeQuery();
             //判斷是否有數(shù)據(jù)
            if(resultSet!=null&&resultSet.next()){
                user=new User();
                user.setUid(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                user.setPhone(resultSet.getString(4));
                user.setEmail(resultSet.getString(5));
                user.setIsDel(resultSet.getInt(6));



            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutils.close(connection,preparedStatement,resultSet);
        }


        return user;
    }

    @Override
    public int insert(User user) {
        //定義變量
        int count=0;
        //建立鏈接
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection = Dbutils.getConnection();
            String sql="insert into user(username, password, phone, email)values(?,?,?,?) ";
            //創(chuàng)建ps對象
            preparedStatement = connection.prepareStatement(sql);
            //設(shè)置占位符參數(shù)
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getPhone());
            preparedStatement.setString(4,user.getEmail());
            count=preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutils.close(connection,preparedStatement);
        }
        return count;
    }

    @Override
    public int updateById(User user) {
        int count = 0;
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = Dbutils.getConnection();
            String sql = "UPDATE  user SET  password=? WHERE uid=?";
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getPassword());
            ps.setInt(2, user.getUid());
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Dbutils.close(connection, ps);
        }
        return count;
    }
}

DetailDaoImpl

package Dao.impl;

import Dao.DetailsDao;
import com.yeungkim.entity.Detail;
import com.yeungkim.entity.User;
import com.yeungkim.utils.Dbutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DetailDaoImpl implements DetailsDao {
    /**
     * 保存用戶信息以及地址詳細(xì)信息
     *
     * @param detail
     * @param user
     * @return
     */
    @Override
    public int insertDetail(Detail detail, User user) {
        int count = 0;
        Connection connection = null;
        PreparedStatement ps = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        ResultSet resultSet = null;

        String userSql = "INSERT  INTO  t_user(username, password, phone, email) VALUES (?,?,?,?)";
        String userSelectSql = "SELECT uid FROM t_user WHERE username=?";
        String sql = "INSERT  INTO  detail(address, uid) VALUES (?,?)";

        try {
            connection = Dbutils.getConnection();
            //開啟事務(wù)
            connection.setAutoCommit(false);

            // 保存用戶信息
            ps1 = connection.prepareStatement(userSql);
            ps1.setString(1, user.getUsername());
            ps1.setString(2, user.getPassword());
            ps1.setString(3, user.getPhone());
            ps1.setString(4, user.getEmail());

            ps1.executeUpdate();

            // 通過用戶信息查詢用戶ID
            ps2 = connection.prepareStatement(userSelectSql);
            ps2.setString(1, user.getUsername());
            resultSet = ps2.executeQuery();
            if (resultSet != null && resultSet.next()) {
                int uid = resultSet.getInt("uid");
                // 保存地址信息
                Integer.parseInt("adbc1");

                ps = connection.prepareStatement(sql);
                ps.setString(1, detail.getAddress());
                ps.setInt(2, uid);
                count = ps.executeUpdate();

                connection.commit();
            }
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                ps1.close();
                ps2.close();
                Dbutils.close(connection, ps, resultSet);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return count;
    }
}

Dbutils-工具類

package com.yeungkim.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Dbutils {
    //步驟：
    //1初始化數(shù)據(jù)庫連接池
    //2獲取數(shù)據(jù)庫連接對象
    //3釋放資源并關(guān)閉對象
    public static final String DB_CONFIG="db.properties";
    public static DataSource dataSource;

    static {
        Properties properties = new Properties();
        InputStream inputStream = Dbutils.class.getClassLoader().getResourceAsStream(DB_CONFIG);

        try {
            properties.load(inputStream);
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            System.out.println("初始化數(shù)據(jù)庫連接池失敗，請檢查配置信息");
        }
    }
    public static Connection getConnection() throws Exception{
        return dataSource.getConnection();

    }

    //釋放資源關(guān)閉連接
    public static void close(Connection connection, Statement statement){
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println("關(guān)閉statement對象失敗");
            }
        }
        if(connection !=null){
            try {
                connection.close();
            } catch (SQLException e) {
                System.out.println("關(guān)閉connetion失敗");
            }
        }
    }


    //Dao data access Object
    //connection
    //statement
    //resultSet
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(connection,statement);
    }

}

主函數(shù)

package com.yeungkim;

import Dao.DetailsDao;
import Dao.UserDao;
import Dao.impl.DetailDaoImpl;
import Dao.impl.UserDaoImpl;
import com.yeungkim.entity.Detail;
import com.yeungkim.entity.User;

import java.util.List;

public class Main {

public static void main(String[] args) {
    users();
    register();
    updatePassword();
    login();
    save()；
    

}

public static void users(){
    //創(chuàng)建userdao對象
    UserDao userDao=new UserDaoImpl();
    //調(diào)用dao方法
    List<User> users=userDao.selectAll();
    System.out.println(users);


}

public static void register(){
    //創(chuàng)建userdao
    UserDao userDao=new UserDaoImpl();
    //創(chuàng)建user對象
    User user=new User();
    user.setUsername("admin");
    user.setPassword("admin");
    user.setPhone("123456");
    user.setEmail("1234@163.com");
    int count=userDao.insert(user);
    if(count>0){
        System.out.println("注冊成功");
    }else{
        System.out.println("注冊失敗");
    }

}
//已知用戶id
public static void updatePassword(){
    UserDao userDao=new UserDaoImpl();
    User user=new User();
    user.setUid(1);
    user.setPassword("123456");
    int count= userDao.updateById(user);
    System.out.println(count);

}

//登錄功能
public static void login(String username,String pwd){
    UserDao userDao=new UserDaoImpl();
    User user=userDao.selectUserByName(username);
    //
    if(user!=null){
        System.out.println("存在用戶");
        if(user.getIsDel()==0){
            if(user.getPassword().equals(pwd)){
                System.out.println("登錄成功");
            }else {
                System.out.println("登錄失敗，賬戶或密碼錯誤");
            }
        }else{
            System.out.println("賬戶被凍結(jié)，請與管理員聯(lián)系");
        }

    }else{
        System.out.println("用戶名不存在");
    }
}

//保存用戶信息及詳細(xì)內(nèi)容

public static void save(){
    DetailsDao detailsDao=new DetailDaoImpl();
    User user=new User();
    user.setUsername("damin");
    user.setPassword("1244");
    user.setPhone("1234");
    user.setEmail("123@13.com");
    Detail detail=new Detail();
    detail.setAddress("四季酒店");
    detailsDao.insertDetail(detail,user);
}

}

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容