2-5 jdbc连接mysql & 通用查询

JDBC实现通用查询

package st_2_1;

import java.sql.*;

public class Conn {
    //数据库URL
    private static final String URL
            = "jdbc:mysql://localhost:3306/mydb?"
            + "useUnicode=true&characterEncoding=utf8&"
            + "serverTimezone=Asia/Shanghai&useSSL=false";
    //数据库用户名
    private static final String USER = "root";
    //数据库用户密码
    private static final String PASSWORD = "123456";
    //数据库连接类
    static Connection conn = null;
    //接收sql语句预编译后返回的对象
    static PreparedStatement ps = null;
    //接收数据库操作返回的结果集
    static ResultSet rs = null;

    //连接初始化 方法
    public static void init(){
        if(conn == null){
            try{
                //反射获取连接驱动类Class对象，根据包路径加载连接驱动类
                Class.forName("com.mysql.cj.jdbc.Driver");
                //建立连接
                conn = DriverManager.getConnection(URL, USER, PASSWORD);
            }catch (Exception e){
                System.out.println("Database connection fail");
                e.printStackTrace();
            }
            System.out.println("Database connection success");
        }else {
            System.out.println("already build database connection");
        }
    }

    //单句查询
    public static void query(String sql){
        try{
            //预编译sql语句
            ps = conn.prepareStatement(sql);
            //执行sql语句,返回结果集
            rs = ps.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getInt("id") + " " + rs.getString("stu_name"));
            }
        }catch (SQLException e){
            System.out.println("sql query error");
            e.printStackTrace();
        }
    }

    //单句更新
    public static void update(String sql){
        try{
            ps = conn.prepareStatement(sql);
            //返回执行sql影响的行数
            int re = ps.executeUpdate();
            System.out.println("success, rows affected: " + re);
        }catch (SQLException e){
            System.out.println("sql modify error");
            e.printStackTrace();
        }
    }

    //占位符（？）插入
    public static void placeholderInsert(String name, int gender){
        String sql = "insert into stu_info set stu_name=?, gender=?";
        try {
            ps = conn.prepareStatement(sql);
            //替换占位符
            ps.setString(1, name);
            ps.setInt(2, gender);
            //execute()执行PreparedStatement的对象中的sql语句，sql语句可以是任意类型。
            /*
            execute() Returns:
                true if the first result is a ResultSet object;
                false if the first result is an update count or there is no result
             */
            System.out.println(ps.execute());
        } catch (SQLException e) {
            System.out.println("sql run error");
            e.printStackTrace();
        }
    }

    //批量插入
    public static void batchInsert(String[] sqls){
        try{
            //设置事务不自动提交，jdbc中默认为true
            conn.setAutoCommit(false);
            Statement st = conn.createStatement();
            //逐句添加sql语句到statement
            for (int i = 0; i < sqls.length; i++){
                st.addBatch(sqls[i]);
            }
            //批量执行
            st.executeBatch();
            //提交事务
            conn.commit();
            System.out.println("sql run success");
        } catch (SQLException e) {
            System.out.println("sql run error");
            e.printStackTrace();
        }
    }

    //插入同时返回输出新插入记录的主键
    public static void returnPKInsert(String sql){
        try{
            //设置执行完操作后返回主键
            ps=conn.prepareStatement(sql, ps.RETURN_GENERATED_KEYS);
            //执行sql影响的行数
            int re = ps.executeUpdate();
            System.out.println("success, rows affected: " + re);
            ResultSet rs = ps.getGeneratedKeys();
            Integer pk = null;
            if(rs.next()){
                pk = rs.getInt(1);
            }
            System.out.println("primary key: " + pk);
        } catch (SQLException e) {
            System.out.println("sql run error");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        //建立连接
        Conn.init();
        //查询
        query("select * from stu_info");
        //修改(update、insert、delete)
        update("insert into stu_info set id = 15, stu_name='小明'");
        //使用占位符插入
        placeholderInsert("小李子", 1);
        //批量插入
        String[] sqls = {
                "insert into stu_info set stu_name='刘备'",
                "insert into stu_info set stu_name='张飞'",
                "insert into stu_info set stu_name='关羽'"
        };
        batchInsert(sqls);
        //插入同时返回输出主键
        returnPKInsert("insert into stu_info set stu_name='诸葛孔明'");
    }
}

JDBC利用泛型实现通用查询

package st_2_1;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Conn2 {
    //数据库URL
    private static final String URL
            = "jdbc:mysql://localhost:3306/mydb?"
            + "useUnicode=true&characterEncoding=utf8&"
            + "serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true";
    //数据库用户名
    private static final String USER = "root";
    //数据库用户密码
    private static final String PASSWORD = "123456";
    //数据库连接类
    static Connection conn = null;
    //接收sql语句预编译后返回的对象
    static PreparedStatement ps = null;
    //接收数据库操作返回的结果集
    static ResultSet rs = null;

    //连接初始化 方法
    public static void init(){
        if(conn == null){
            try{
                //反射获取连接驱动类Class对象，根据包路径加载连接驱动类
                Class.forName("com.mysql.cj.jdbc.Driver");
                //建立连接
                conn = DriverManager.getConnection(URL, USER, PASSWORD);
            }catch (Exception e){
                System.out.println("Database connection fail");
                e.printStackTrace();
            }
            System.out.println("Database connection success");
        }else {
            System.out.println("already build database connection");
        }
    }

    /*
        将查询到的结果集转换成自定义类的对象，并存在在List中
        <T>声明此方法持有一个类型T，此方法为一个泛型方法
        Class<T> cla指明泛型T的具体类型
        params是一个Object类型的数组
    */
    public static <T> List<T> queryAll(String sql, Class<T> cla, Object... params){
        try{
            //预编译
            ps = conn.prepareStatement(sql);
            if(params != null){
                for(int i = 0; i < params.length; ++i){
                    //替换指定位置的占位符
                    ps.setObject(i+1, params[i]);
                }
            }
            //执行查询
            rs = ps.executeQuery();
            //获取描述结果集基础信息（列数、数据类型、字段）的meta元数据
            ResultSetMetaData rsd = rs.getMetaData();
            //返回值
            List<T> list = new ArrayList<>();
            while (rs.next()){
                //实例化一个泛型类
                T t = cla.newInstance();
                for (int i = 0; i < rsd.getColumnCount(); ++i){
                    //获取列名，sql语句中有别名返回别名，否则返回值与getColumnName()方法相同
                    String column = rsd.getColumnLabel(i+1);
                    //根据列名取值
                    Object value = rs.getObject(column);
                    //根据索引取值
                    //Object value = rs.getObject(i+1);
                    //根据列名获取属性
                    Field field = cla.getDeclaredField(convertFormat(column));
                    //开启私有变量访问权限
                    field.setAccessible(true);
                    //给对象t的变量赋值
                    field.set(t, value);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    //下划线命名转小驼峰命名
    public static String convertFormat(String source){
        String[] strings = source.split("_");
        StringBuffer sf = new StringBuffer(strings[0]);
        for(int i = 1; i < strings.length; ++i){
            sf.append(strings[i].substring(0,1).toUpperCase() + strings[i].substring(1));
        }
        return sf.toString();
    }

    public static void main(String[] args) {
        Conn2.init();
        String sql = "select * from stu_info where id = ? and stu_name = ?";
        List<Student> list = queryAll(sql, Student.class, 14, "小明");
//        String sql = "select * from stu_info where gender=?";
//        List<Student> list = queryAll(sql, Student.class, 1);
        for(Student s: list){
            System.out.println(s);
        }
    }
}

class Student{
    //id不可能为null，可以用基本数据类型接收
    private int id;
    private String stuName;
    //可能为null的属性用包装类接收
    private Integer gender;
    private Integer claId;
    private Integer grade;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", StuName='" + stuName + '\'' +
                ", gender=" + gender +
                ", claId=" + claId +
                ", grade=" + grade +
                '}';
    }
}