JDBC操作Mysql数据库

工作用到的数据库挺多， 常用已涉及sql server，oracle ，mysql 。 这些操作数据库功能类似又不尽相同， 现记录下Java操作Mysql的样例。本实例主要使用参数化查询sql，或调用存储过程返回数据

1  数据准备创建数据表， 添加数据。   

DROP TABLE IF EXISTS `t1`;CREATE TABLE `t1` (  `ID` INT(11) NOT NULL AUTO_INCREMENT,  `Code` VARCHAR(20) DEFAULT NULL,  `Name` VARCHAR(20) DEFAULT NULL,  `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`ID`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO t1(`Code`,`Name`) VALUES('cjr','陈佳仁');

2 导入包

导入  mysql-connector-java-5.1.39-bin.jar   包下载地址

3 添加一个简易Jdbc帮助类，实现获取连接，关闭连接功能。

package cjr.util;import java.io.FileInputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JdbcUtil {private static String connectString="jdbc:mysql://localhost:3306/test0629?characterEncoding=utf8&useSSL=true";private static String user ="root";private static String pwd ="";private static String driver ="com.mysql.jdbc.Driver";static{String path = Class.class.getClass().getResource("/").getPath()+"db.properties";Properties pro = new Properties();FileInputStream in;try {in = new FileInputStream(path);pro.load(in);driver = pro.getProperty("driver");connectString = pro.getProperty("connection");user = pro.getProperty("user");pwd = pro.getProperty("pwd");Class.forName(driver);} catch (Exception e) {// TODO: handle exceptionSystem.out.println("error");}}public static Connection getConnection(){Connection con=null;try {con = DriverManager.getConnection(connectString, user, pwd);} catch (Exception e) {System.out.println("error getConnection");}return con;}public static void close(Connection con,Statement stmt,ResultSet rs){if(rs!=null)try {rs.close();rs=null;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}if(stmt!=null)try {stmt.close();stmt=null;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {if(con!=null&&!con.isClosed())try {con.close();con=null;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void close(Connection con,Statement stmt){close(con, stmt, null);}}

4  Jdbc操作

4.1 新增数据，并返回新增的id

<span style="white-space:pre"></span>@Testpublic void userAdd(){String code = "zs";String name = "张三";String sql = "INSERT INTO t1(`Code`,`Name`) VALUES(?,?)";Connection con = null;PreparedStatement pstmt = null;int id;try {con = JdbcUtil.getConnection();pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);pstmt.setString(1, code);pstmt.setString(2, name);pstmt.executeUpdate();ResultSet rs = pstmt.getGeneratedKeys();if(rs.next()){ id = rs.getInt(1); System.out.println("插入的数据为："+id+","+code+","+name);}} catch (SQLException e) {System.out.println("error");e.printStackTrace();}finally{JdbcUtil.close(con, pstmt);}}

输出结果：

插入的数据为：2,zs,张三

4.2 修改操作

<span style="white-space:pre"></span>@Testpublic void userEdit(){String code = "cjh";String name = "陈津海";int user_id = 1;String sql = "UPDATE t1 SET CODE=?,NAME=?,TIME=NOW() WHERE id = ?";Connection con = null;PreparedStatement pstmt = null;try {con = JdbcUtil.getConnection();pstmt = con.prepareStatement(sql);pstmt.setString(1, code);pstmt.setString(2, name);pstmt.setInt(3, user_id);/*** 参数可以通过Object类型设置简单参数pstmt.setObject(1, code);pstmt.setObject(2, name);pstmt.setObject(3, user_id);*/int cnt = pstmt.executeUpdate();System.out.println("成功执行了" + cnt + "行");} catch (SQLException e) {System.out.println("error");e.printStackTrace();}finally{JdbcUtil.close(con, pstmt);}}

运行结果：

成功执行了1行

4.3 删除操作

@Testpublic void userDel(){int user_id = 8;String sql = "delete from t1 WHERE id = ?";Connection con = null;PreparedStatement pstmt = null;try {con = JdbcUtil.getConnection();pstmt = con.prepareStatement(sql);pstmt.setInt(1, user_id);int cnt = pstmt.executeUpdate();System.out.println("成功删除了" + cnt + "行");} catch (SQLException e) {System.out.println("error");e.printStackTrace();}finally{JdbcUtil.close(con, pstmt);}}

运行结果：

成功删除了1行

4.4 通过sql查询操作

<span style="white-space:pre"></span>@Testpublic void getUserById(){int user_id = 2;String sql = "select * from t1 WHERE id = ?";Connection con = null;PreparedStatement pstmt = null;ResultSet rs = null;try {con = JdbcUtil.getConnection();pstmt = con.prepareStatement(sql);pstmt.setInt(1, user_id);rs = pstmt.executeQuery();while(rs.next()){System.out.println("用户信息："+ rs.getInt("id")+","+ rs.getString("CODE")+","+ rs.getString("Name")+","+ rs.getDate("Time") );}} catch (SQLException e) {System.out.println("error");e.printStackTrace();}finally{JdbcUtil.close(con, pstmt);}}

运行结果：

用户信息：2,zs,张三,2016-06-29

4.4 通过存储过程查询操作

1 创建存储过程 ，含输出参数的存储过程

DROP PROCEDURE IF EXISTS Get_user_list ;DELIMITER $$CREATE  PROCEDURE Get_user_list(p_id INT,OUT p_count INT)    #含输出参数BEGINSELECT COUNT(*) INTO p_count FROM t1 WHERE id >= p_id ;SELECT * FROM t1 WHERE id >= p_id;END $$

2 在查询编辑器调用存储过程

CALL Get_user_list(1,@p_count);
SELECT @p_count;

3 java代码操作

<span style="white-space:pre"></span>@Testpublic void getUserList(){int p_id=1;Connection con = null;CallableStatement cstmt = null;ResultSet rs = null;try {con = JdbcUtil.getConnection();cstmt = con.prepareCall("call Get_user_list(?,?)");cstmt.setInt(1, p_id);cstmt.registerOutParameter(2, java.sql.Types.INTEGER);rs = cstmt.executeQuery();System.out.println("总记录行数："+ cstmt.getInt(2));while(rs.next()){System.out.println("用户信息："+ rs.getInt("id")+","+ rs.getString("CODE")+","+ rs.getString("Name")+","+ rs.getDate("Time") );}} catch (Exception e) {System.out.println("getUserList error");}finally{JdbcUtil.close(con, cstmt, rs);}}

运行结果：

总记录行数：2
用户信息：1,cjr,陈佳仁,2016-06-29
用户信息：2,zs,张三,2016-06-29

4.5 批量操作

<pre name="code" class="java">@Testpublic void userBatchAdd(){String code = "ls";String name = "李四";String sql = "INSERT INTO t1(`Code`,`Name`) VALUES(?,?)";Connection con = null;PreparedStatement pstmt = null;try {con = JdbcUtil.getConnection();pstmt = con.prepareStatement(sql);for(int i=0;i<10;i++){pstmt.setString(1, code+i);pstmt.setString(2, name+i);pstmt.addBatch(); }pstmt.executeBatch();System.out.println("批量插入成功");} catch (SQLException e) {System.out.println("error");e.printStackTrace();}finally{JdbcUtil.close(con, pstmt);}}

运行结果：

批量插入成功

可在数据库查看新增了10条数据，   批量操作的只执行一次executeBatch()。

4.6 事务操作

连接Connection默认是自动提交的，需将其设置为手动提交

<span style="white-space:pre"></span>@Testpublic void userDelTransation(){Connection con = null;PreparedStatement pstmt = null;try {con = JdbcUtil.getConnection();con.setAutoCommit(false);pstmt = con.prepareStatement("delete from t1 where id = 7");pstmt.executeUpdate();System.out.println("执行第一条语句成功！");pstmt = con.prepareStatement("delete1 form t1 where id = 6");pstmt.executeUpdate();System.out.println("执行第二条语句成功！");con.commit();System.out.println("执行成功并提交");} catch (Exception e) {try {if(con != null){con.rollback();System.out.println("执行失败，已回滚");}} catch (SQLException e1) {e1.printStackTrace();}}finally{JdbcUtil.close(con, pstmt);}}

运行结果

执行第一条语句成功！
执行失败，已回滚

备注： 第一条语句执行成功，第二条语句执行失败， 因此数据被回滚。  可以修改第二条sql语句：delete from t1 where id = 6

执行成功并提交数据。

设置参数时： pstmt.setObject(1, code);   可以通过setObject方法设置int ,String 等参数