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 block
				e1.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 stmt
			stmt = 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 添加sql
				stmt.addBatch();
				// 注意：如果使用批处理的话，一定要指定范围，不然容易内存泄露/溢出
				int batchSize = 1000;
				int count = 0;
				if (++count >= batchSize) {
					stmt.executeBatch();// 一旦batchSize等于1000的时候，直接执行
					count = 0;
				}
			}
			// 7 执行批处理
			// 一定要加上，最后那一次可能小于1000
			stmt.executeBatch();
			stmt.close();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			conn.rollback();
			throw e;
		} finally {
			conn.close();
		}
	}

}