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标准规范 实例

　　（一）源码

  1 import java.sql.*;
  2 import java.util.*;
  3 
  4 class Db{
  5     String url = "jdbc:mysql://localhost:3306/dbtao";
  6     String username = "root";
  7     String password = "123465";
  8     String sql;
  9     PreparedStatement pst = null;
 10     ResultSet rs = null;
 11     Connection conn = null;
 12     
 13     public Db(){
 14         this("");
 15     }
 16     public Db(String sql){
 17         this.sql = sql;
 18     }
 19     public boolean Dbclose(){
 20         boolean f = true;
 21         if(rs != null){
 22             try{
 23                 rs.close();
 24             }catch(SQLException e) {
 25                 e.printStackTrace();
 26                 f = false;
 27             }
 28             rs = null;
 29         }
 30         if(pst != null){
 31             try{
 32                 pst.close();
 33             }catch(SQLException e) {
 34                 e.printStackTrace();
 35                 f = false;
 36             }
 37             pst = null;
 38         }
 39         if(conn != null){
 40             try{
 41                 conn.close();
 42             }catch(SQLException e) {
 43                 e.printStackTrace();
 44                 f = false;
 45             }
 46             conn = null;
 47         }
 48         return f;
 49     }
 50     
 51     public boolean DbPrepared(){
 52         if(sql.isEmpty()){
 53             System.out.println("SQL语句未设置!");
 54             return false;
 55         }
 56         try {
 57             Class.forName("com.mysql.jdbc.Driver");
 58             conn = DriverManager.getConnection(url, username, password);
 59             pst = conn.prepareStatement(sql);
 60             return true;
 61         } catch (Exception e) {
 62             e.printStackTrace();
 63             Dbclose();
 64             return false;
 65         }
 66     }
 67 }
 68 
 69 class Emp {
 70     public int empno;
 71     public String ename;
 72     public String job;
 73     public int mgr;
 74     public Timestamp hiredate;
 75     public double sal;
 76     public double comm;
 77     public int deptno;
 78     
 79     public Emp(int empno, String ename, String job, int mgr, Timestamp hiredate, double sal, double comm, int deptno){
 80         this.empno = empno;
 81         this.ename = ename;
 82         this.job = job;
 83         this.mgr = mgr;
 84         this.hiredate = hiredate;
 85         this.sal = sal;
 86         this.comm = comm;
 87         this.deptno = deptno;
 88     }
 89     
 90     public Emp(){
 91         this(0,null,null,0,null,0,0,0);
 92     }
 93     
 94     public String toString(){
 95         return empno + "\t" + ename + "\t" + job + "\t" + mgr + "\t" + 
 96                 hiredate + "\t" + sal + "\t" + comm + "\t" + deptno;
 97     }
 98     
 99     public static Db db;
100     public static Emp getByEmpno(int empno){
101         db = new Db("SELECT * FROM emp WHERE empno=?");
102         if(db.DbPrepared()){
103             try {
104                 db.pst.setObject(1, empno);
105                 db.rs = db.pst.executeQuery();
106                 if(db.rs.next()){
107                     Emp emp = new Emp();
108                     emp.empno = db.rs.getInt("empno");
109                     emp.ename = db.rs.getString("ename");
110                     emp.job = db.rs.getString("job");
111                     emp.mgr = db.rs.getInt("mgr");
112                     emp.hiredate = db.rs.getTimestamp("hiredate");
113                     emp.sal = db.rs.getDouble("sal");
114                     emp.comm = db.rs.getDouble("comm");
115                     emp.deptno = db.rs.getInt("deptno");
116                     
117                     db.Dbclose();
118                     return emp;
119                 }
120                 
121             } catch (SQLException e) {
122                 db.Dbclose();
123                 e.printStackTrace();
124                 System.out.println(e.getMessage());
125                 return null;
126             }
127         }
128         System.out.println("未查询到该编号的员工。");
129         return null;
130     }
131 
132     public static List<Emp> getByEname(String ename){
133         db = new Db("SELECT * FROM emp WHERE ename like ?");
134         if(db.DbPrepared()){
135             try {
136                 db.pst.setObject(1, "%"+ename+"%");
137                 db.rs = db.pst.executeQuery();
138                 List<Emp> list = new LinkedList<Emp>();
139                 while(db.rs.next()){
140                     Emp emp = new Emp();
141                     emp.empno = db.rs.getInt("empno");
142                     emp.ename = db.rs.getString("ename");
143                     emp.job = db.rs.getString("job");
144                     emp.mgr = db.rs.getInt("mgr");
145                     emp.hiredate = db.rs.getTimestamp("hiredate");
146                     emp.sal = db.rs.getDouble("sal");
147                     emp.comm = db.rs.getDouble("comm");
148                     emp.deptno = db.rs.getInt("deptno");
149                     list.add(emp);
150                 }
151                     db.Dbclose();
152                     return list;
153                 
154             } catch (SQLException e) {
155                 db.Dbclose();
156                 e.printStackTrace();
157                 System.out.println(e.getMessage());
158                 return null;
159             }
160         }
161         System.out.println("未查询到该编号的员工。");
162         return null;
163     }
164 
165     public static int add(Emp emp){
166         db = new Db("insert into emp values(?,?,?,?,?,?,?,?)");
167         if(db.DbPrepared()){
168             try {
169                 db.pst.setObject(1, emp.empno);
170                 db.pst.setObject(2, emp.ename);
171                 db.pst.setObject(3, emp.job);
172                 db.pst.setObject(4, emp.mgr);
173                 db.pst.setObject(5, emp.hiredate);
174                 db.pst.setObject(6, emp.sal);
175                 db.pst.setObject(7, emp.comm);
176                 db.pst.setObject(8, emp.deptno);
177                 int t = db.pst.executeUpdate();
178                     
179                 db.Dbclose();
180                 return t;
181                 
182             } catch (SQLException e) {
183                 db.Dbclose();
184                 e.printStackTrace();
185                 System.out.println(e.getMessage());
186                 return 0;
187             }
188         }
189         System.out.println("插入失败。");
190         return 0;
191     }
192     
193     public static int updateByEmpno(Emp emp, int empno){
194         db = new Db("update emp set ename=?,job=?,mgr=?,hiredate=?,"+
195                  "sal=?,comm=?,deptno=? where empno=?");
196         if(db.DbPrepared()){
197             try {
198                 db.pst.setObject(8, emp.empno);
199                 db.pst.setObject(1, emp.ename);
200                 db.pst.setObject(2, emp.job);
201                 db.pst.setObject(3, emp.mgr);
202                 db.pst.setObject(4, emp.hiredate);
203                 db.pst.setObject(5, emp.sal);
204                 db.pst.setObject(6, emp.comm);
205                 db.pst.setObject(7, emp.deptno);
206                 int t = db.pst.executeUpdate();
207                     
208                 db.Dbclose();
209                 return t;
210                 
211             } catch (SQLException e) {
212                 db.Dbclose();
213                 e.printStackTrace();
214                 System.out.println(e.getMessage());
215                 return 0;
216             }
217         }
218         System.out.println("更新失败。");
219         return 0;
220     }
221     public static int deleteByEmpno(int empno){
222         db = new Db("delete from emp where empno=?");
223         if(db.DbPrepared()){
224             try {
225                 db.pst.setObject(1, empno);
226                 int t = db.pst.executeUpdate();
227                     
228                 db.Dbclose();
229                 return t;
230                 
231             } catch (SQLException e) {
232                 db.Dbclose();
233                 e.printStackTrace();
234                 System.out.println(e.getMessage());
235                 return 0;
236             }
237         }
238         System.out.println("删除失败。");
239         return 0;
240     }
241 }
242 
243 public class JDBC {
244 
245     public static void main(String[] args) {
246         String head = "编号\t姓名\t职位\t领导编号\t入职时间\t\t\t薪资\t提成\t部门\n";
247         
248         Emp e = new Emp();
249         e.empno = 1;
250         e.ename = "dks";
251         e.job = "dj";
252         e.mgr = 16;
253         e.hiredate = new Timestamp(System.currentTimeMillis());
254         e.sal = 1.1;
255         e.comm = 1.2;
256         e.deptno = 0;
257 
258         System.out.println("插入 "+Emp.add(e)+" 条数据");
259         
260         System.out.println(head);
261         System.out.println(Emp.getByEmpno(1));
262         
263         e.deptno = 5;
264         System.out.println("更新 "+Emp.updateByEmpno(e, 1)+" 条数据");
265         
266         List<Emp> list = Emp.getByEname("s");
267         System.out.println(head);
268         for(int i = 0; i < list.size(); i++){
269             System.out.println(list.get(i));
270         }
271         
272         System.out.println("删除 "+Emp.deleteByEmpno(1)+" 条数据");
273         list = Emp.getByEname("s");
274         System.out.println(head);
275         for(int i = 0; i < list.size(); i++){
276             System.out.println(list.get(i));
277         }
278         
279     }
280 
281 }

　　（二）运行结果

 

posted @ 2019-05-28 15:17  MoonTwilight  阅读(348)  评论(0)  编辑  收藏  举报