Day1_JDBC——增删改

一.分析

由上一篇Day1_JDBC——PreparedStatement可以看出，我们只需要改变sql语句的内容，就可以实现对数据库中的数据进行增删改操作。

二.例子

2.1 增

JDBCUtils.java

package com.atguigu.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
        private static String driverClass;
        private static String url;
        private static String user;
        private static String password;
        
        /*只进行加载一次数据
         *
         */
        static {
        	//1.提取配置文件
        	Properties info = new Properties();
        	//使用当前类的类加载器去类路径下加载配置文件info.properties并转换为输入流
        	InputStream is = JDBCUtils.class
        	         .getClassLoader()
        	         .getResourceAsStream("info.properties");
        	try {
				info.load(is);
				driverClass = info.getProperty("driverClass");
				url = info.getProperty("url");
				user = info.getProperty("user");
				password = info.getProperty("password");
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        
	   /*
	        * 获取数据库连接的方法
	    */
	    public static Connection getConn() {
	    	Connection conn = null;
	    	try {
				conn = DriverManager.getConnection(url, user, password);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    	return conn;
	    }
	    
	    /*关闭连接
	     *  
	     */
	    public static void closeConn(Connection conn) {
	    	if(conn != null) {
	    		try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	    	}
	    }
	    
	    /*
	         * 释放连接
	     */
	    public static void releaseConn(ResultSet rs, Statement st, Connection conn) {
	    	if(rs != null) {
	  	    	   try {
						rs.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
	  	       }
	    	if(st != null) {
	  	    	   try {
						st.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
	  	       }
	    	if(conn != null) {
	  	    	   try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
	  	       }
		    	 
	    }
	    
}

TestJDBC02.java

package com.atguigu.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.junit.jupiter.api.Test;

import com.atguigu.utils.JDBCUtils;

class TestJDBC02 {
    
	/*
	 * 实现增加数据添加记录的方法
	 */
	@Test
	public void test01() {
           //1.获取数据库连接
		   Connection conn = JDBCUtils.getConn();
		   PreparedStatement ps = null;
		   String sql = "insert into users (id, username, password, balance)"
				       + "values (?, ?, ?, ?)";
		   //2.创建sql命令发送对象
		   try {
		    ps = conn.prepareStatement(sql);
		    
		    ps.setObject(1, 5);//设置占位符的具体参数
			ps.setObject(2, "liliu");//设置占位符的具体参数
			ps.setObject(3, "123");
			ps.setObject(4, 100);
			//3.执行sql并解析返回的结果
			int n = ps.executeUpdate();
			System.out.println(n);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.releaseConn(null, ps, conn);
		}
		   
	}

}

2.2 删

TestJDBC02.java

/**
			 * 删除记录操作
			 */
			@Test
			public void test02() {
				//1.获取数据库连接
				Connection conn = JDBCUtils.getConn();
				PreparedStatement ps = null;
				String sql = "delete from users where username = ?";
				try {
					//2.创建sql命令发送对象
					ps = conn.prepareStatement(sql);
					ps.setObject(1, "liliu");
					//3.执行更新
					int n = ps.executeUpdate();
					System.out.println(n);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					JDBCUtils.releaseConn(null, ps, conn);
				}
			}
			

2.3改

TestJDBC02.java

/**
			 * 修改记录操作
			 */
			@Test
			public void test03() {
				//1.获取数据库连接
				Connection conn = JDBCUtils.getConn();
				PreparedStatement ps = null;
				String sql = "update users set id = ?, username = ?, password = ?, "
						+ " balance = ? where id = ?";
//				String sql = "update users set id = ?, username = ?, password = ?,"
//		    	   		+ " balance = ? where id = ?";
				try {
					//2.创建sql命令发送对象
					ps = conn.prepareStatement(sql);
					ps.setObject(1, 4);//设置参数
					ps.setObject(2, "liliu");//设置参数
					ps.setObject(3, "321");//设置参数
					ps.setObject(4, 110);//设置参数
					ps.setObject(5, 6);//设置参数
					//3.执行更新
					int n = ps.executeUpdate();
					System.out.println(n);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					JDBCUtils.releaseConn(null, ps, conn);
				}
			}