Java连接MySQL数据库命令行程序过程

目录

• 创建表
• 创建命令行程序
• 下载并导入jar包
• 创建工具包
• 创建实体类
• 创建持久化层
• 业务层调用
• 总结

CREATE TABLE student(
Sno VARCHAR(10) PRIMARY KEY,
Sname VARCHAR(50),
Ssex   VARCHAR(2), 
Sage   INT,
Sdept VARCHAR(50)
)

public class MySqlConnection {
    private static Connection con=null;
    private static String driveName ="com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/sl?serverTimezone=GMT%2b8";
    private static String user="root";
    private static String pws="root";
    
    private  MySqlConnection(){}
    public static Connection getCon(){
       if(con==null){
           con = getConnection();
       }
       return con;
    }
    private static Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driveName);
            connection = DriverManager.getConnection(url,user,pws);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            return connection;
        }
    }
}

public class MysqlHelp {
    public  static ResultSet query(String sql)  {
        Connection con = MySqlConnection.getCon();
        ResultSet result =null;

        try {
            Statement stmt = con.createStatement();
            result= stmt.executeQuery(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            return result;
        }
    }
}

@Data
public class Student {
    private String sno;
    private String sName;
    private String sSex;
    private int sAge;
    private String sDept;

    public Student(String sno,String name, String sex,int age,String dept){
        this.setSno(sno);
        this.setSAge(age);
        this.setSName(name);
        this.setSDept(dept);
        this.setSSex(sex);
    }

    public String getInfo(){
        return "学号："+sno +"姓名："+sName+"性别："+sSex+"年龄："+"院系："+sDept;
    }
}

public class StudentDao {
    public void getStudentInfo(){
        String sql="SELECT Sno AS sno,Sname AS NAME,Ssex AS sex,Sage AS age,Sdept AS dept FROM student";

        ResultSet result = MysqlHelp.query(sql);
        if(result!=null) {
            while (true) {
                try {
                    if (!result.next()) break;
                    String sno = result.getString("sno");
                    String name = result.getString("name");
                    int age = result.getInt("age");
                    String dept = result.getString("dept");
                    String sex = result.getString("sex");
                    Student student = new Student(sno, name, sex, age, dept);
                    System.out.println(student.getInfo());
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
        }
    }

public class Main {

    public static void main(String[] args) {
        StudentDao student = new StudentDao();
        student.getStudentInfo();
    }
}