jdbc使用（配置版）

一、创建properties.properties文件连接好数据库

点击查看代码

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo01
username=root
password=root
zero=0

二、创建DbHelper.java类连接properties.properties文件

点击查看代码

public class DbHelper {
    public static String DRIVER;
    public static String URL;
    public static String USERNAME;
    public static String PASSWORD;
    public static int ZERO;

    static{
        InputStream inputStream = DbHelper.class.getResourceAsStream("properties.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        DRIVER = properties.getProperty("driver");
        URL = properties.getProperty("url");
        USERNAME = properties.getProperty("username");
        PASSWORD = properties.getProperty("password");
        ZERO = Integer.parseInt(properties.getProperty("zero"));
    }

}

三、创建DbManager类，找驱动、建连接

点击查看代码

public class DbManager {
    static Connection getConnection(){
        try {
            Class.forName(DbHelper.DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection con = null;
        try {
            con = DriverManager.getConnection(DbHelper.URL,DbHelper.USERNAME,DbHelper.PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
}

四、创建一个接口IMapper.java

点击查看代码

public interface IMapper {
    List mapper(ResultSet rs);
}

五、创建实体类Student与数据库对接

点击查看代码

public class Student {
    private int id;
    private String name;

    public Student() {
    }

    public Student(int id,String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

六、创建类StudentMapper实现接口IMapper

点击查看代码

public class StudentMapper implements IMapper{
    @Override
    public List mapper(ResultSet rs) {
        List<Student> list = new ArrayList<>();
        Student stu = null;
        try {
            while(rs.next()){
                stu = new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

七、创建一个Execute类创建通道、执行sql语句并返回结果（包含更新方法和查询方法）

点击查看代码

public class Execute {

    public int executeUpdate(String sql, List<Object> objectList){
        PreparedStatement ps = null;
        int num = DbHelper.ZERO;
        Connection con = DbManager.getConnection();
        try {
            ps = con.prepareStatement(sql);
            if(objectList != null){
                for (int i = 0; i< objectList.size(); i++) {
                    ps.setObject(i+1, objectList.get(i));
                }
            }
            num = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return num;
    }

    public List executeQuery(String sql, IMapper imapper, List<Object> objectList){
        PreparedStatement ps = null;
        ResultSet rs = null;
        List list = null;
        try {
            ps = DbManager.getConnection().prepareStatement(sql);
            if(objectList != null){
                for (int i = 0; i< objectList.size(); i++) {
                    ps.setObject(i+1, objectList.get(i));
                }
            }
            rs = ps.executeQuery();
            list = imapper.mapper(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try {
                rs.close();
                ps.close();
                DbManager.getConnection().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }
}

八、创建StudentDao写sql语句，调用Execute类方法传参数返回结果

点击查看代码

public class StudentDao {
    int insert(){
        String sql = "insert into student values(3, 'wang')";
        Execute exec = new Execute();
        int num = exec.executeUpdate(sql, null);
        return num;
    }

    int update(){
        String sql = "update student set name = 'li' where id = 3";
        Execute exec = new Execute();
        int num = exec.executeUpdate(sql, null);
        return num;
    }

    int delete(){
        String sql = "delete from student where id = 3";
        Execute exec = new Execute();
        int num = exec.executeUpdate(sql, null);
        return num;
    }

    List<Student> select(){
        String sql = "select * from student";
        Execute exec = new Execute();
        StudentMapper studentMapper = new StudentMapper();
        List<Student> list = exec.executeQuery(sql, studentMapper, null);
        return list;
    }

    List<Student> selectById(int id){
        // ?表示占位符
        String sql = "select * from student where id = ?";
        Execute exec = new Execute();
        StudentMapper studentMapper = new StudentMapper();
        List<Object> listObject = new ArrayList<Object>();
        listObject.add(id);
        List<Student> list = exec.executeQuery(sql, studentMapper, listObject);
        return list;
    }
}

九、测试

点击查看代码

public class Test {
    public static void main(String[] args) throws SQLException {

        Execute exec = new Execute();

//        String sql = "insert into student values(2, 'zhang')";
//        int num = exec.executeUpdate(sql);
        StudentDao studentDao = new StudentDao();
        List<Student> list = studentDao.selectById(1);

        for (Student stu : list){
            System.out.println(stu);
        }
    }
}