jdbc2016

查询

package com.cydiguo.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestJDBC {public static void main(String[] args) {Connection con = null;try {Class.forName("com.mysql.jdbc.Driver");con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=870304&useUnicode=true&characterEncoding=UTF8");String sql = "select t.* from student t where t.id=?";PreparedStatement stmt = con.prepareStatement(sql);stmt.setInt(1, 1);ResultSet rs = stmt.executeQuery();while (rs.next()) {System.out.println(rs.getString("name"));}rs.close();stmt.close();} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();} finally {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}

修改

package com.cydiguo.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestJDBC {public static void main(String[] args) {Connection con = null;try {Class.forName("com.mysql.jdbc.Driver");con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=870304&useUnicode=true&characterEncoding=UTF8");String sql = "update student set name='new' where id=? ";PreparedStatement stmt = con.prepareStatement(sql);stmt.setInt(1, 1);int i = stmt.executeUpdate();System.out.println(i);stmt.close();} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();} finally {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}

保存

package com.cydiguo.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestJDBC {public static void main(String[] args) {Connection con = null;try {Class.forName("com.mysql.jdbc.Driver");con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=870304&useUnicode=true&characterEncoding=UTF8");String sql = "insert into student(id,name) values(3,'Hhh') ";PreparedStatement stmt = con.prepareStatement(sql);stmt.executeUpdate();stmt.close();} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();} finally {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}

删除

package com.cydiguo.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestJDBC {public static void main(String[] args) {Connection con = null;try {Class.forName("com.mysql.jdbc.Driver");con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=870304&useUnicode=true&characterEncoding=UTF8");String sql = "delete from student where id=? ";PreparedStatement stmt = con.prepareStatement(sql);stmt.setInt(1, 1);int i = stmt.executeUpdate();//返回执行的条数System.out.println(i);stmt.close();} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();} finally {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}

事务

默认自动提交事务

package com.cydiguo.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestJDBC {public static void main(String[] args) {Connection con = null;try {Class.forName("com.mysql.jdbc.Driver");con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root&password=870304&useUnicode=true&characterEncoding=UTF8");// 控制事务： 首先要把这个事务改为手动提交con.setAutoCommit(false);// 开启事务String sql = "delete from student where id=? ";PreparedStatement stmt = con.prepareStatement(sql);stmt.setInt(1, 1);int i = stmt.executeUpdate();// 返回执行的条数System.out.println(i);stmt.close();// 提交事务con.commit();} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();try {con.rollback();} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}} finally {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}

批处理

package com.cydiguo.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestJDBC {public void saveBatch(List<ServiceLiu> list) throws Exception {Connection conn = null;PreparedStatement stmt = null;try {// 1 获得连接conn = getConnection();// 2 编写sql语句String sql = "insert into Service_liu3 " + "(ID,SERVICE_ID,HOST,OS_USERNAME,PID,LOGIN_TIME,"+ " LOGOUT_TIME,DURATION,COST) " + " values (SERVICE_DETAIL_SEQ.nextval,?,?,?,?,?,?,?,?)";// 3 控制事务conn.setAutoCommit(false);// 4 stmtstmt = conn.prepareStatement(sql);// 5 添加参数for (ServiceLiu serviceLiu : list) {stmt.setInt(1, serviceLiu.getServiceId());stmt.setString(2, serviceLiu.getHost());stmt.setString(3, serviceLiu.getOsusername());stmt.setInt(4, serviceLiu.getPid());stmt.setTimestamp(5, new Timestamp(serviceLiu.getLoginTime().getTime()));stmt.setTimestamp(6, new Timestamp(serviceLiu.getLogOutTime().getTime()));stmt.setInt(7, serviceLiu.getDuartion());stmt.setInt(8, serviceLiu.getCost());// 6 添加sqlstmt.addBatch();// 注意：如果使用批处理的话，一定要指定范围，不然容易内存泄露/溢出int batchSize = 1000;int count = 0;if (++count >= batchSize) {stmt.executeBatch();// 一旦batchSize等于1000的时候，直接执行count = 0;}}// 7 执行批处理// 一定要加上，最后那一次可能小于1000stmt.executeBatch();stmt.close();conn.commit();} catch (SQLException e) {e.printStackTrace();conn.rollback();throw e;} finally {conn.close();}}}