JDBC三步连接MySql

一、导入依赖文件、编写配置文件

在依赖文件中添加JDBC的依赖文件：

如果是Maven项目也可以在Pom.xml中导入依赖

文件下载：https://pan.baidu.com/s/1ktMMhzG8cHKOa_2VpcqvrQ?pwd=882i 提取码：882i 

然后新建application.properties文件编写数据库配置（也可以直接将数据库配置写在下一步的连接方法里面）。

二、编写数据库连接代码

使用getConnection函数获取数据库连接句柄，使用disConnection关闭数据库连接。

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class DataBaseConnect {

    public static Connection getConnection() {
        Properties properties = new Properties();
        try{
            FileInputStream fileInputStream = new FileInputStream("src\\application.properties");
            properties.load(fileInputStream);
        } catch (IOException e){
            e.printStackTrace();
            return null;
        }

        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        try{
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(url, user, password);
        }catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void disConnection(Connection conn, Statement st, AutoCloseable resource) {
        try{
            if(resource != null){
                resource.close();
            }
            if(st != null){
                st.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (Exception e){
            e.printStackTrace();
        }
    }
}

 三、编写数据库增删查改函数

其中set中对应的数字对应sql语句中的'?'，get中的数字对应的是数据库表中的数据顺序。

import entity.student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MySQLFunction {

    public static int count(String tableName) {
        try{
            Connection conn = DataBaseConnect.getConnection();

            if(conn != null) {
                String sql = "select count(*) from "+ tableName +" ";
                int num = 0;

                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(sql);
                while(rs.next()){
                    num = rs.getInt(1);
                }
                DataBaseConnect.disConnection(conn, st, rs);

                return num;
            }
            else {
                return -1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }

    public static List<student> searchAll(String tableName) {
        try{
            Connection conn = DataBaseConnect.getConnection();

            if(conn != null) {
                String sql = "select * from "+ tableName +" ";

                List<student> studentList = new ArrayList<>();

                int id;
                String name;
                int age;
                int hobby_id;

                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(sql);
                while(rs.next()){
                    id = rs.getInt(1);
                    name = rs.getString(2);
                    age = rs.getInt(3);
                    hobby_id = rs.getInt(4);
                    studentList.add(new student(id, name, age, hobby_id));
                }

                DataBaseConnect.disConnection(conn, st, rs);

                return studentList;
            }
            else {
                return null;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static boolean insert(int id, String name, int age, Integer hobby_id) {
        try{
            Connection conn = DataBaseConnect.getConnection();
            if(conn != null){
                String sql;
                if(!(id >= 10000 && id <= 99999)) {
                    sql = "insert into student(name, age, hobby_id) values(?,?,?)";
                    PreparedStatement pst=conn.prepareStatement(sql);
                    pst.setString(1, name);
                    pst.setInt(2, age);
                    if (hobby_id != null) {
                        pst.setInt(3, hobby_id);
                    } else {
                        pst.setNull(3, Types.INTEGER);
                    }
                    pst.execute();
                    DataBaseConnect.disConnection(conn, null, pst);
                }
                else {
                    sql = "insert into student(id, name, age, hobby_id) values(?,?,?,?)";
                    PreparedStatement pst=conn.prepareStatement(sql);
                    pst.setInt(1, id);
                    pst.setString(2, name);
                    pst.setInt(3, age);
                    if (hobby_id != null) {
                        pst.setInt(4, hobby_id);
                    } else {
                        pst.setNull(4, Types.INTEGER);
                    }
                    pst.execute();
                    DataBaseConnect.disConnection(conn, null, pst);
                }
                return true;
            }
            return false;

        } catch (SQLException e){
            e.printStackTrace();
            return false;
        }
    }

    public static boolean deleteById(int id){
        try{
            Connection conn = DataBaseConnect.getConnection();
            if(conn != null){
                String sql="delete from student where id=?";
                PreparedStatement pst=conn.prepareStatement(sql);
                pst.setInt(1, id);
                pst.execute();
                DataBaseConnect.disConnection(conn, null, pst);
                return true;
            }
            else{
                return false;
            }
        } catch (SQLException e){
            e.printStackTrace();
            return false;
        }
    }

    public static boolean modifyById(int id, int age){
        try{
            Connection conn = DataBaseConnect.getConnection();
            if(conn != null){
                String sql="update student set age=? where id=?";
                PreparedStatement pst=conn.prepareStatement(sql);
                pst.setInt(1, age);
                pst.setInt(2, id);
                pst.execute();
                DataBaseConnect.disConnection(conn, null, pst);
                return true;
            }
            else{
                return false;
            }
        } catch (SQLException e){
            e.printStackTrace();
            return false;
        }
    }

}

 四、测试连接

public class Insert {
    public static void main(String[] args) {
        // id表中自增，如果不能确定id到哪儿了，或者查询时间开销比较大就传入-1
        if(MySQLFunction.insert(-1,"hinagiku",15,null)){
            System.out.println("insert succeed");
        }
    }
}

其他代码：

public class student {
    private int id;
    private String name;
    private int age;
    private int hobbyId;

    public student(int _id, String _name, int _age, int _hobbyId){
        this.id = _id;
        this.name = _name;
        this.age = _age;
        this.hobbyId = _hobbyId;
    }

    public String getName() { return name; }

    public int getAge() { return age; }

    public int getId() { return id; }

    public int getHobby_id() { return hobbyId; }

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

    public void setAge(int age) { this.age = age; }

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

    public void setHobbyId(int hobbyId) { this.hobbyId = hobbyId; }
}

项目结构：