JDBC连接Mysql(附完整代码)

一、创建maven工程

1、选择quickStart

2、导入mysql-connector-java的依赖

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>

二、程序以供参考

1、先在根目录下创建config/jdbc.Properties，如下：

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/你的库名?useSSL=true&useUnicode=true&characterEncoding=utf8
mysql.username=root
mysql.password=xxxxxxxxxxx

2、BaseDao类（仅供参考）

public class BaseDao {
    private static final Properties properties = new Properties();
    private static final  String PATH="config/jdbc.properties";

    static {
        try {
            properties.load(new FileReader(PATH));
            Class.forName(properties.getProperty("mysql.driver"));
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(-1);
        }
    }

    private static Connection con() throws SQLException {
        return DriverManager.getConnection(
          properties.getProperty("mysql.url"),
          properties.getProperty("mysql.username"),
          properties.getProperty("mysql.password")
        );
    }

    private static PreparedStatement pst(Connection con,String sql,Object...objs) throws SQLException {
        PreparedStatement pst = con.prepareStatement(sql);
        if (null != objs && objs.length>0){
            for (int i = 0; i < objs.length; i++) {
                pst.setObject(i+1,objs[i]);
            }
        }
        return pst;
    }

    private static void  close(AutoCloseable...closes){
        for (AutoCloseable close : closes) {
            if (null != close){
                try {
                    close.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //执行增删改操作
    public static int exeUpdate(String sql ,Object...objs){
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = con();
            pst = pst(con,sql,objs);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(pst,con);
        }
        return -1;
    }

    //执行批量插入
    public  static int addBatch(String sql,int cloSize,int batchSize,Object...objs){
        Connection con= null;
        PreparedStatement pst = null;
        int affactedRows=0;
        try {
            con = con();
            pst = con.prepareStatement(sql);
            for (int i = 0,realSize = 0; i < objs.length; i+=cloSize) {
                for (int j = i,size=0; size <cloSize ;size++, j++) {
                    pst.setObject(size+1,objs[j]);
                }
                pst.addBatch();
                if (++realSize%batchSize==0){
                    affactedRows = pst.executeBatch().length;
                    pst.clearBatch();
                }
                affactedRows = pst.executeBatch().length;
                pst.clearBatch();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(pst,con);
        }
        return -1;
    }

    //查询输出
    public  static void exeQuery(String sql){
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rst = null;
        try {
            con = con();
            pst = con.prepareStatement(sql);
            rst = pst.executeQuery();
            int colSize = rst.getMetaData().getColumnCount();
            while (rst.next()){
                for (int i = 1; i <= colSize ; i++) {
                    System.out.print(rst.getObject(i).toString());
                    System.out.print("\t");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(rst,pst,con);
        }
    }

}

3、Test类（具体以你的需求来写）

public class Test{
    public static void main(String[] args) {
        BaseDao baseDao= new BaseDao();

        //单条数据导入
        /*String sql = "insert into studentinfo(stuId,stuName,stuAge,StuGender,fkClassId) values(?,?,?,?,?)";
        BaseDao.exeUpdate(sql,30,"戴敏","23","男",1);*/

        //批量导入
        /*String sql = "insert into studentinfo(stuId,stuName,stuAge,StuGender,fkClassId) values(?,?,?,?,?)";
        Object[] arr = {12,"戴敏","24","男",1,13,"张","25","男",1}
        BaseDao.addBatch(sql,5,2,arr);*/

        //查询
        /*String sql = "select empName,min(empId) from employee group by empPos";
        BaseDao.exeQuery(sql);*/

        //删除
        /*String sql = "delete from studentinfo where stuId like ?";
        BaseDao.exeUpdate(sql,30);*/

        //修改
        String sql = "update studentinfo set stuName= ?,StuGender = ? where stuId = 31";
        BaseDao.exeUpdate(sql,"戴敏","男");

    }
}