使用JDBC执行 select insert update delete

[java]代码库

package demo.mysql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import demo.utils.JDBCUtils;

/*
 使用JDBC执行 select insert update delete
 */
public class Demo1 {

	@Test
	public void testSelect() throws Exception{
		//注册驱动(数据库的类型)
		/*
		 * 这种方式存在的问题：
		 * 1. 依赖于jar包
		 * 2. 导致驱动com.mysql.jdbc.Driver注册两次
		 */
		//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		//实际开发，应该采用下面的方式注册驱动  --> Java反射
		Class.forName("com.mysql.jdbc.Driver");

		//获取连接
		String url = "jdbc:mysql://localhost:3306/jdbc";
		String user = "root";
		String password = "password";
		Connection conn = DriverManager.getConnection(url, user, password);

		//得到运行环境
		Statement st = conn.createStatement();

		//执行SQL
		String sql = "select * from myuser";
		ResultSet rs = st.executeQuery(sql);

		//处理结果
		while(rs.next()){
			//每次循环，都取出一条记录
			int userID = rs.getInt("userID");
			String name = rs.getString("userName");
			String userPass = rs.getString("userPass");
			String email = rs.getString("email");
			Date birthday = rs.getDate("birthday");
			
			System.out.println(userID);
			System.out.println(name);
			System.out.println(userPass);
			System.out.println(email);
			System.out.println(birthday);
			System.out.println("*********************");
		}

		//释放资源
		rs.close();
		st.close();
		conn.close();
	}

	@Test
	public void testInsert(){
		String url = "jdbc:mysql://localhost:3306/jdbc";
		String user = "root";
		String password = "password";
		
		Connection conn = null;
		Statement st = null;
		
		try{
			//注册驱动(数据库的类型)
			Class.forName("com.mysql.jdbc.Driver");
	
			//获取连接
			conn = DriverManager.getConnection(url, user, password);
			
			//得到运行环境
			st = conn.createStatement();
	
			//执行SQL
			String sql = "insert into myuser(userID,userName) values("+5+",'Tom'"+")";
			System.out.println("sql="+sql);
			int result = st.executeUpdate(sql);
	
			//处理结果
			if(result>0){
				System.out.println("操作成功");
			}else{
				System.out.println("操作失败");
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			//释放资源
			if(st != null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					st = null;//--> 让他迅速成为java gc的对象
				}
			}
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					conn = null;//--> 让他迅速成为java gc的对象
				}
			}
		}

	}
	
	@Test
	public void testUpate(){
		String sql = "update myuser set username='Tom_AB' where userID=4";
		
		Connection conn = null;
		Statement st = null;
		try {
			//从工具类中获取连接
			conn = JDBCUtils.getConnection();
			st = conn.createStatement();
			int result = st.executeUpdate(sql);
			//处理结果
			if(result>0){
				System.out.println("操作成功");
			}else{
				System.out.println("操作失败");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//调用工具类释放资源
			JDBCUtils.release(conn, st, null);
		}

	}
	
	@Test
	public void testDelete(){
		String url = "jdbc:mysql://localhost:3306/jdbc";
		String user = "root";
		String password = "password";
		
		Connection conn = null;
		Statement st = null;
		
		try{
			//注册驱动(数据库的类型)
			Class.forName("com.mysql.jdbc.Driver");
	
			//获取连接
			conn = DriverManager.getConnection(url, user, password);
			
			//得到运行环境
			st = conn.createStatement();
	
			//执行SQL
			String sql = "delete from myuser where userID=5";
			System.out.println("sql="+sql);
			int result = st.executeUpdate(sql);
	
			//处理结果
			if(result>0){
				System.out.println("操作成功");
			}else{
				System.out.println("操作失败");
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			//释放资源
			if(st != null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					st = null;//--> 让他迅速成为java gc的对象
				}
			}
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					conn = null;//--> 让他迅速成为java gc的对象
				}
			}
		}

	}
}