【JDBC】CRUD操作

JDBC的CRUD操作

• 向数据库中保存记录
• 修改数据库中的记录
• 删除数据库中的记录
• 查询数据库中的记录

保存代码的实现

package demo1;

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

import org.junit.Test;

public class JDBCDemo3 {
	
	@Test
	/**
	 * 保存代码的实现
	 */
	public void demo3() {
		Connection conn = null;
		Statement stmt = null;
		
		try {
			//注册驱动
			Class.forName("com.mysql.cj.jdbc.Driver");
			//获得连接
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest?serverTimezone=Hongkong", "root", "1234");
			//获得执行SQL语句的对象
			stmt = conn.createStatement();
			//编写SQL
			String sql = "insert user values(null,'ddd','444','赵六')";
			//执行SQL
			int i = stmt.executeUpdate(sql);
			if(i > 0) {
				System.out.println("保存成功！");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally{
			//释放资源
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stmt = null;
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
	}
}

修改代码的实现

package demo1;

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

import org.junit.Test;

public class JDBCDemo3 {
	
	@Test
	/**
	 * 修改代码的实现
	 */
	public void demo2() {
		Connection conn = null;
		Statement stmt = null;
		
		try {
			//注册驱动
			Class.forName("com.mysql.cj.jdbc.Driver");
			//获得连接
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest?serverTimezone=Hongkong", "root", "1234");
			//获得执行SQL语句的对象
			stmt = conn.createStatement();
			//编写SQL
			String sql = "update user set username='qqq',password='1234',name='或无言' where uid=4";
			//执行SQL
			int i = stmt.executeUpdate(sql);
			if(i > 0) {
				System.out.println("修改成功！");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally{
			//释放资源
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stmt = null;
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
	}
}

删除代码的实现

package demo1;

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

import org.junit.Test;

public class JDBCDemo3 {
	
	@Test
	/**
	 * 删除代码的实现
	 */
	public void demo1() {
		Connection conn = null;
		Statement stmt = null;
		
		try {
			//注册驱动
			Class.forName("com.mysql.cj.jdbc.Driver");
			//获得连接
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest?serverTimezone=Hongkong", "root", "1234");
			//获得执行SQL语句的对象
			stmt = conn.createStatement();
			//编写SQL
			String sql = "delete from user where uid=4";
			//执行SQL
			int i = stmt.executeUpdate(sql);
			if(i > 0) {
				System.out.println("删除成功！");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally{
			//释放资源
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stmt = null;
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
	}
}

查询代码的实现

package demo1;

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

import org.junit.Test;

public class JDBCDemo1 {
	
	@Test
	/**
	 * 查询代码的实现
	 */
	public void demo1() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			//1.加载驱动
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			//2.获得连接
			conn = DriverManager.getConnection("jdbc:mysql:///jdbctest?serverTimezone=Hongkong", "root", "1234");
			
			//3创建执行SQL语句的对象，并且执行SQL
			//3.1创建执行SQL语句的对象
			String sql = "select * from user";
			stmt = conn.createStatement();
			//3.2执行SQL
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				int uid = rs.getInt("uid");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String name = rs.getString("name");
				
				System.out.println(uid + "-" + username + "-" + password + "-" + name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//释放资源
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				rs = null;
			}
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				stmt = null;
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
	}
}