jdbc2016

最新推荐文章于 2021-02-16 20:23:40 发布

烟雨青山

最新推荐文章于 2021-02-16 20:23:40 发布

阅读量501

点赞数 1

CC 4.0 BY-SA版权

分类专栏： jdbc

本文链接：https://blog.youkuaiyun.com/qq1036053871/article/details/52675727

jdbc 专栏收录该内容

3 篇文章

订阅专栏

本文提供了一系列关于Java JDBC的基本操作实例，包括查询、修改、保存、删除数据及事务和批处理的处理方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询

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();
		}
	}

}