欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

JDBC之Java连接mysql实现增删改查

程序员文章站 2022-07-10 11:34:08
使用软件:mysql、eclipse 链接步骤: 1.注册驱动 2.创建一个连接对象 3.写sql语句 4.执行sql语句并返回一个结果或者结果集 5.关闭链接(一般就是connection、statement、setresult)这三个连接对象,关闭顺序一般是(setresult > statem ......

使用软件:mysql、eclipse

链接步骤:

1.注册驱动 

2.创建一个连接对象

3.写sql语句

4.执行sql语句并返回一个结果或者结果集

5.关闭链接(一般就是connection、statement、setresult)这三个连接对象,关闭顺序一般是(setresult    --->  statement  -->  setresult  )

一、直接连接方法:(这种方法就是讲sql语句和结果所有的步骤写在一起) 不建议使用该方法

 1 public static void main(string[] args) {
 2         string url = "jdbc:mysql://localhost:3306/students";
 3         string user = "root";
 4         string password = "admin";
 5         connection conn = null;
 6         statement st = null;
 7         
 8         try {
 9             // 1. 注册驱动
10             class.forname("com.mysql.jdbc.driver");
11             // 2. 创建一个链接对象
12             conn = drivermanager.getconnection(url,user,password);
13             // 3. 创建一个sql语句的发送命令对象
14             string sql = "insert into student values('2001','tom','20','7000')";        
15             st= conn.createstatement();
16             // 4. 执行sql语句,拿到查询的结果集对象
17             st.executequery(sql);20         } catch (exception e) {
21             e.printstacktrace();
22         }finally {
23             // 5. 关闭链接 ,命令对象 ,结果集
24             if(st != null) {
25                 try {
26                     st.close();
27                 } catch (exception e) {
28                     e.printstacktrace();
29                 }
30             }
31             if(conn != null) {
32                 try {
33                     conn.close();
34                 } catch (exception e) {
35                     e.printstacktrace();
36                 }
37             }
38         }

二、建立工具类方法,将必要的几步写一个类,使用的时候直接调用建议使用

1.注册驱动、创建连接对象、关闭资源    这三部一般可以写一个类,由于写sql语句和执行sql语句的结果不一致,所以可以将其在用到的时候在写

2.一般写工具类都是写成静态方法,以方便调用

//这是工具类:

import java.sql.connection; import java.sql.drivermanager; import java.sql.resultset; import java.sql.statement; public class jdbcutils { private static string drivername = "com.mysql.jdbc.driver"; private static string url = "jdbc:mysql://localhost:3306/student_achievement_system"; private static string user = "root"; private static string password = "admin"; /** * 链接数据库 */ static { try { class.forname(jdbcutils.drivername); } catch (exception e) { e.printstacktrace(); } } /** * 获取链接对象connection * @return */ public static connection getconnection() { try { return drivermanager.getconnection(jdbcutils.url, jdbcutils.user, jdbcutils.password); } catch (exception e) { e.printstacktrace(); } return null; } /** * 关闭资源 * @param conn * @param st * @param rs */ public static void close(connection conn,statement st,resultset rs) { if(rs != null) { try { rs.close(); } catch (exception e) { e.printstacktrace(); } } if(st != null) { try { st.close(); } catch (exception e) { e.printstacktrace(); } } if(conn != null) { try { conn.close(); } catch (exception e) { e.printstacktrace(); } } } }

  

//这是对数据库的基本操作类

// 增加、删除、更新、查找一条、查找所有的方法

public class studentsdaoimpl implements istudentsdao {     //增加 @override public int save(students student) { connection conn = null; preparedstatement ps = null; try { conn = jdbcutils.getconnection(); string sql = "insert into students values(?,?,?,?,?,?)"; ps = conn.preparestatement(sql); ps.setint(1, student.getstudentid()); ps.setstring(2, student.getstudentname()); ps.setstring(3, student.getsex()); ps.setstring(4, student.getphoneno()); ps.setstring(5, student.getaddress()); ps.setdate(6, (date) student.getbirthday()); int row = ps.executeupdate(); return row; } catch (exception e) { e.printstacktrace(); }finally { jdbcutils.close(conn, ps, null); } return 0; }       //删除 @override public int delete(int studentid) { connection conn = null; preparedstatement ps = null; try { conn = jdbcutils.getconnection(); string sql = "delete from students where studentid=?"; ps = conn.preparestatement(sql); ps.setint(1, studentid); int row = ps.executeupdate(); return row; } catch (exception e) { e.printstacktrace(); }finally { jdbcutils.close(conn, ps, null); } return 0; }     //更新 @override public int update(int studentid, students student) { connection conn = null; preparedstatement ps = null; try { conn = jdbcutils.getconnection(); string sql = "update students set studentname=?,sex=?,phoneno=?,address=?,birthday=? where studentid=?"; ps = conn.preparestatement(sql); ps.setstring(1, student.getstudentname()); ps.setstring(2, student.getsex()); ps.setstring(3, student.getphoneno()); ps.setstring(4, student.getaddress()); ps.setdate(5, ((date) student.getbirthday())); ps.setint(6, studentid); int row = ps.executeupdate(); return row; } catch (exception e) { e.printstacktrace(); }finally { jdbcutils.close(conn, ps, null); } return 0; }       //查找一条数据 @override public students getbystudentid(int studentid) { connection conn = null; preparedstatement ps = null; resultset rs = null; try { conn = jdbcutils.getconnection(); string sql = "select * from students where studentid=?"; ps = conn.preparestatement(sql); ps.setint(1, studentid); rs = ps.executequery(); if(rs.next()) { students student = new students(); student.setstudentid(rs.getint("studentid")); student.setstudentname(rs.getstring("studentname")); student.setsex(rs.getstring("sex")); student.setphoneno(rs.getstring("phoneno")); student.setaddress(rs.getstring("address")); student.setbirthday(rs.getdate("birthday")); return student; } } catch (exception e) { e.printstacktrace(); }finally { jdbcutils.close(conn, ps, rs); } return null; }       //查找所有数据 @override public list<students> getall() { connection conn = null; preparedstatement ps = null; resultset rs = null; try { conn = jdbcutils.getconnection(); string sql = "select * from students"; ps = conn.preparestatement(sql); rs = ps.executequery(); list<students> studentslist = new arraylist<>(); while(rs.next()) { students student = new students(); student.setstudentid(rs.getint("studentid")); student.setstudentname(rs.getstring("studentname")); student.setsex(rs.getstring("sex")); student.setphoneno(rs.getstring("phoneno")); student.setaddress(rs.getstring("address")); student.setbirthday(rs.getdate("birthday")); studentslist.add(student); } return studentslist; } catch (exception e) { e.printstacktrace(); }finally { jdbcutils.close(conn, ps, rs); } return null; }
}