Java 学习笔记 一 -- JDBC的SUN标准规范

一、数据库准备和Java环境配置

　　（一）安装MySQL、Navicat、JDK、Eclipse

　　（二）配置Java环境变量

　　（三）导入 jar（mysql-connection-java -> Build Path）

二、SUN标准规范

　　（一）加载驱动

　　　　将 Driver 类加载到 jvm 内存中，初始化驱动管理器 DriverManager

Class.forName("com.mysql.jdbc.Driver"); //DriverManager

　　（二）通过驱动管理器获取数据库连接

String url = "jdbc:mysql://127.0.0.1:3306/databaseDemo?useSSL=false";
String user = "root";
String password = "123456";

　　（三）获取数据库连接，执行 SQL语句并获取结果集

Connection conn = DriverManager.getConnection(url, user, password);

//存在SQL注入
//String sql = "select * from table where id=" + id;
//Statement st = conn.createStatement(); 
//ResultSet rs = st.executeQuery(sql);

String sql = "select * from table where id=?";
PreparedStatement pst = conn.preparedStatement(sql);
st.setObject(1, id);
//int rs = pst.executeUpdate(); //增删改
ResultSet rs = pst.executeQuery(); //查

　　（四）对结果集进行处理(获取日期时间时用时间戳 Timestamp)

while(rs.next()){
    System.out.println(rs.getInt("id"));
    System.out.println(rs.getString("name"));
    System.out.println(rs.getTimestamp("datetime"));
　　//getDate 只能获取年月日，getTime 只能获取时分秒
}

　　（五）释放数据库连接(后开的先关)

//前面还是try一下，SUN标准规范就是这么恶心
finally{
    if(rs != null){
        try{
            rs.close();
        }catch(SQLException e) {
            e.printStackTrace();
        }
        rs = null;
    }
    if(stmt != null){
        try{
            stmt.close();
        }catch(SQLException e) {
            e.printStackTrace();
        }
        stmt = null;
    }
    if(conn != null){
        try{
            conn.close();
        }catch(SQLException e) {
            e.printStackTrace();
        }
        conn = null;
    }    
}

 三、一些需要注意的地方

　　（一）Java传入MySQL datetime类型

//java传入String
st.setString(1, "2019-05-28 20:10:10");

//java传入Date
//只改日期
st.setDate(1, new java.sql.Date(System.currentTimeMillis()));
//改日期和时刻
st.setTimestamp(1, new Timestamp(System.currentTimeMillis()));

//Java 中Timestamp 类型传入MySQL数据库5.7以上版本时
Timestamp t = new Timestamp(System.currentTimeMillis());
//此时 t 的默认toString方法因为有毫秒数，高版本MySQL会限制解析
//传入时必须用（"yyy-MM-dd hh-mm-ss"），例：
t.toLocaleString()
t.toString().substring(0, 19)

　　（二）PreparedStatement 使用 like 模糊查询

　　使用 PreparedStatement 进行模糊查找时，不能直接在 sql语句中写入  "---like '%?%'"，需要在 set值时添加 %符

String sql = "SELECT * FROM emp WHERE ename like ?";
pst.setObject(1, "%"+ename+"%");

 四、SUN标准规范 实例

　　（一）源码

import java.sql.*;
import java.util.*;

class Db{
    String url = "jdbc:mysql://localhost:3306/dbtao";
    String username = "root";
    String password = "123465";
    String sql;
    PreparedStatement pst = null;
    ResultSet rs = null;
    Connection conn = null;
    
    public Db(){
        this("");
    }
    public Db(String sql){
        this.sql = sql;
    }
    public boolean Dbclose(){
        boolean f = true;
        if(rs != null){
            try{
                rs.close();
            }catch(SQLException e) {
                e.printStackTrace();
                f = false;
            }
            rs = null;
        }
        if(pst != null){
            try{
                pst.close();
            }catch(SQLException e) {
                e.printStackTrace();
                f = false;
            }
            pst = null;
        }
        if(conn != null){
            try{
                conn.close();
            }catch(SQLException e) {
                e.printStackTrace();
                f = false;
            }
            conn = null;
        }
        return f;
    }
    
    public boolean DbPrepared(){
        if(sql.isEmpty()){
            System.out.println("SQL语句未设置!");
            return false;
        }
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            pst = conn.prepareStatement(sql);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            Dbclose();
            return false;
        }
    }
}

class Emp {
    public int empno;
    public String ename;
    public String job;
    public int mgr;
    public Timestamp hiredate;
    public double sal;
    public double comm;
    public int deptno;
    
    public Emp(int empno, String ename, String job, int mgr, Timestamp hiredate, double sal, double comm, int deptno){
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }
    
    public Emp(){
        this(0,null,null,0,null,0,0,0);
    }
    
    public String toString(){
        return empno + "	" + ename + "	" + job + "	" + mgr + "	" + 
                hiredate + "	" + sal + "	" + comm + "	" + deptno;
    }
    
    public static Db db;
    public static Emp getByEmpno(int empno){
        db = new Db("SELECT * FROM emp WHERE empno=?");
        if(db.DbPrepared()){
            try {
                db.pst.setObject(1, empno);
                db.rs = db.pst.executeQuery();
                if(db.rs.next()){
                    Emp emp = new Emp();
                    emp.empno = db.rs.getInt("empno");
                    emp.ename = db.rs.getString("ename");
                    emp.job = db.rs.getString("job");
                    emp.mgr = db.rs.getInt("mgr");
                    emp.hiredate = db.rs.getTimestamp("hiredate");
                    emp.sal = db.rs.getDouble("sal");
                    emp.comm = db.rs.getDouble("comm");
                    emp.deptno = db.rs.getInt("deptno");
                    
                    db.Dbclose();
                    return emp;
                }
                
            } catch (SQLException e) {
                db.Dbclose();
                e.printStackTrace();
                System.out.println(e.getMessage());
                return null;
            }
        }
        System.out.println("未查询到该编号的员工。");
        return null;
    }

    public static List<Emp> getByEname(String ename){
        db = new Db("SELECT * FROM emp WHERE ename like ?");
        if(db.DbPrepared()){
            try {
                db.pst.setObject(1, "%"+ename+"%");
                db.rs = db.pst.executeQuery();
                List<Emp> list = new LinkedList<Emp>();
                while(db.rs.next()){
                    Emp emp = new Emp();
                    emp.empno = db.rs.getInt("empno");
                    emp.ename = db.rs.getString("ename");
                    emp.job = db.rs.getString("job");
                    emp.mgr = db.rs.getInt("mgr");
                    emp.hiredate = db.rs.getTimestamp("hiredate");
                    emp.sal = db.rs.getDouble("sal");
                    emp.comm = db.rs.getDouble("comm");
                    emp.deptno = db.rs.getInt("deptno");
                    list.add(emp);
                }
                    db.Dbclose();
                    return list;
                
            } catch (SQLException e) {
                db.Dbclose();
                e.printStackTrace();
                System.out.println(e.getMessage());
                return null;
            }
        }
        System.out.println("未查询到该编号的员工。");
        return null;
    }

    public static int add(Emp emp){
        db = new Db("insert into emp values(?,?,?,?,?,?,?,?)");
        if(db.DbPrepared()){
            try {
                db.pst.setObject(1, emp.empno);
                db.pst.setObject(2, emp.ename);
                db.pst.setObject(3, emp.job);
                db.pst.setObject(4, emp.mgr);
                db.pst.setObject(5, emp.hiredate);
                db.pst.setObject(6, emp.sal);
                db.pst.setObject(7, emp.comm);
                db.pst.setObject(8, emp.deptno);
                int t = db.pst.executeUpdate();
                    
                db.Dbclose();
                return t;
                
            } catch (SQLException e) {
                db.Dbclose();
                e.printStackTrace();
                System.out.println(e.getMessage());
                return 0;
            }
        }
        System.out.println("插入失败。");
        return 0;
    }
    
    public static int updateByEmpno(Emp emp, int empno){
        db = new Db("update emp set ename=?,job=?,mgr=?,hiredate=?,"+
                 "sal=?,comm=?,deptno=? where empno=?");
        if(db.DbPrepared()){
            try {
                db.pst.setObject(8, emp.empno);
                db.pst.setObject(1, emp.ename);
                db.pst.setObject(2, emp.job);
                db.pst.setObject(3, emp.mgr);
                db.pst.setObject(4, emp.hiredate);
                db.pst.setObject(5, emp.sal);
                db.pst.setObject(6, emp.comm);
                db.pst.setObject(7, emp.deptno);
                int t = db.pst.executeUpdate();
                    
                db.Dbclose();
                return t;
                
            } catch (SQLException e) {
                db.Dbclose();
                e.printStackTrace();
                System.out.println(e.getMessage());
                return 0;
            }
        }
        System.out.println("更新失败。");
        return 0;
    }
    public static int deleteByEmpno(int empno){
        db = new Db("delete from emp where empno=?");
        if(db.DbPrepared()){
            try {
                db.pst.setObject(1, empno);
                int t = db.pst.executeUpdate();
                    
                db.Dbclose();
                return t;
                
            } catch (SQLException e) {
                db.Dbclose();
                e.printStackTrace();
                System.out.println(e.getMessage());
                return 0;
            }
        }
        System.out.println("删除失败。");
        return 0;
    }
}

public class JDBC {

    public static void main(String[] args) {
        String head = "编号	姓名	职位	领导编号	入职时间			薪资	提成	部门
";
        
        Emp e = new Emp();
        e.empno = 1;
        e.ename = "dks";
        e.job = "dj";
        e.mgr = 16;
        e.hiredate = new Timestamp(System.currentTimeMillis());
        e.sal = 1.1;
        e.comm = 1.2;
        e.deptno = 0;

        System.out.println("插入 "+Emp.add(e)+" 条数据");
        
        System.out.println(head);
        System.out.println(Emp.getByEmpno(1));
        
        e.deptno = 5;
        System.out.println("更新 "+Emp.updateByEmpno(e, 1)+" 条数据");
        
        List<Emp> list = Emp.getByEname("s");
        System.out.println(head);
        for(int i = 0; i < list.size(); i++){
            System.out.println(list.get(i));
        }
        
        System.out.println("删除 "+Emp.deleteByEmpno(1)+" 条数据");
        list = Emp.getByEname("s");
        System.out.println(head);
        for(int i = 0; i < list.size(); i++){
            System.out.println(list.get(i));
        }
        
    }

}

　　（二）运行结果