手把手教你实现一个JDBC程序

一、JDBC的操作步骤

(1)导入Mysql的驱动jar包

(2)加载驱动字符串

(3)获取数据库的连接对象：connection

(4)由连接对象获取语句对象：statement

(5)由语句对象执行sql语句得到结果集对象：resultSet

(6)遍历结果集对象

二、对上述步骤进行java的封装

(1)将数据库的驱动字符串、连接字符串、用户名、密码等信息写入属性(资源文件)文件中

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mvc?useSSl=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
mysql.user=root
mysql.password=123456

(2)在Java的源程序中读取资源文件

代码如下（示例）：

    static {//静态初始化器
        properties = new Properties();
        ClassLoader classLoader = DBMySQL.class.getClassLoader();
        InputStream is = classLoader.getResourceAsStream("jdbc.properties");
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

(3)定义类：将获取数据库的连接对象、语句对象;关闭这两个对象的方法进行封装

package com.tools;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBMySQL {
    private static Properties properties = null;
    private Connection connection = null;

    static {//静态初始化器
        properties = new Properties();
        ClassLoader classLoader = DBMySQL.class.getClassLoader();
        InputStream is = classLoader.getResourceAsStream("jdbc.properties");
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //关闭数据库连接对象
    public void releaseConnection(){
        try {
            if(connection!=null)
            {
            connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //定义方法获得数据库的连接对象
    public Connection getMysqlConnection(){
        String driver = properties.getProperty("mysql.driver").trim();
        String url = properties.getProperty("mysql.url").trim();
        String user = properties.getProperty("mysql.user").trim();
        String password = properties.getProperty("mysql.password").trim();
        try {
            //加载驱动
            Class.forName(driver);
            if(connection ==null){
                //连接数据库，获取连接对象
                connection = DriverManager.getConnection(url,user,password);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
}

(4)创建实体类：一个实体类对应数据库中的一张表,表的列就是实体类的属性

package com.tools;

public class Student {
    private  String sid;
    private  String sname;
    private Integer sage;
    private String sex;

    public Student(String sid, String sname, Integer sage, String sex) {
        this.sid = sid;
        this.sname = sname;
        this.sage = sage;
        this.sex = sex;
    }
    public Student() {
    }

    @Override
    public String toString() {
        return
                "学号='" + sid + '\'' +
                ", 姓名='" + sname + '\'' +
                ", 年龄=" + sage +
                ", 性别='" + sex + '\'' +
                '}';
    }

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public Integer getSage() {
        return sage;
    }

    public void setSage(Integer sage) {
        this.sage = sage;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }
}

(5)定义接口方法

import com.tools.Student;
import java.util.List;
public interface IStudentDao {
    public boolean insert(Student s);
    public boolean delete (String id);
    public boolean update(Student s);
    public List<Student> findAll();
}

(6)定义接口的实现类实现接口方法（增删改查）

package com.impl;

import com.crud.IStudentDao;
import com.tools.DBMySQL;
import com.tools.Student;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoimpl implements IStudentDao {
    private Connection con =null;
    private PreparedStatement ps =null;
    private ResultSet rs = null;

   @Override
   public boolean insert(Student s) {
        DBMySQL dbms= new DBMySQL();
        String sql = "insert into stu values(?,?,?,?)";
        //获取数据库连接对象
        con = dbms.getMysqlConnection();
        int row =0;
        try {
            //获取语句对象，同时对sql语句进行预编译
            ps = con.prepareStatement(sql);
            ps.setString(1,s.getSid());
            ps.setString(2,s.getSname());
            ps.setString(4,s.getSex());
            ps.setInt(3,s.getSage());
            row =ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return  row > 0;
    }

    @Override
    public boolean delete(String id) {
       DBMySQL dbms = new DBMySQL();
       con = dbms.getMysqlConnection();
       int row = 0;
       String sql = "delete from stu where sid = ?";
        try {
            ps =con.prepareStatement(sql);
            ps.setString(1,id);
            row =ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return row>0;
    }

    @Override
    public boolean update(Student s) {
       DBMySQL dbms = new DBMySQL();
       con = dbms.getMysqlConnection();
       String sql = "update stu set sid =? ,age =?,gender =? where sname =?";
       int row = 0;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1,s.getSid());
            ps.setInt(2,s.getSage());
            ps.setString(3,s.getSex());
            ps.setString(4,s.getSname());
            row = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return row > 0;
    }

    @Override
    public List<Student> findAll() {
        List<Student> studentList = new ArrayList<>();
        DBMySQL dbms = new DBMySQL();
        String query_sql = "select * from stu";
        con = dbms.getMysqlConnection();//获取数据库的连接对象
        try {
            ps = con.prepareStatement(query_sql);//获取语句对象，同时对sql语句进行预编译
            rs = ps.executeQuery();

            //遍历结果集：将结果集中的每行数据封装到一个Student对象中
            while(rs.next()){
                Student s = new Student();
                s.setSid(rs.getString("sid"));
                s.setSname(rs.getString("sname"));
                s.setSage(rs.getInt("age"));
                s.setSex(rs.getString("gender"));
                studentList.add(s);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return studentList;
    }
}

(7)进行测试

package com.impl;

import com.crud.IStudentDao;
import com.tools.Student;
import org.junit.Before;
import org.junit.Test;
import java.util.Iterator;
import java.util.List;

public class Main {
    private IStudentDao dao  = null;
    //在执行test方法前会去执行Before里的方法
    @Before
     public void  aa() {
         dao = new StudentDaoimpl();
    }
        @Test
        public void a() {
            List<Student> list = dao.findAll();
            Iterator<Student> it = list.iterator();
            while (it.hasNext()) {
                System.out.println(it.next());
            }
        }

    @Test
    public void in(){
        IStudentDao studentDao = new StudentDaoimpl();
        Student s = new Student("s_1002","刘备",25,"男");
        boolean flag = studentDao.insert(s);
        if(flag){
            System.out.println("插入成功");
        }
        else{
            System.out.println("插入失败");
        }
    }
    @Test
    public void delete(){
            boolean flag = dao.delete("s_1002");
            if(flag){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
    }
    @Test
    public void update(){
            Student s = new Student("s_1003","张三",26,"男");
            boolean flag  = dao.update(s);
            if(flag){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
    }

}

(8)执行结果

学号='s_1003', 姓名='张三', 年龄=26, 性别='男'}
学号='1001', 姓名='李四', 年龄=15, 性别='男'}