JDBC的CRUD操作

JDBC的CRUD操作

• 向数据库中保存记录

@Test
	/**
	 * 保存操作
	 */
	public void demo1() {
		Connection conn = null;
		Statement stmt = null;
		try {
			//注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获得连接
			conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest","root","123456");
			//获得执行SQL语句的对象
			stmt = (Statement) conn.createStatement();
			//编写SQL
			String sql = "insert into user values(null,'eee','123','张三')";
			//执行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 (Exception e) {
					e.printStackTrace();
				}
				stmt = null;
			}
			
			if(conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				conn = null;
			}
		}
	}

• 修改数据库中的记录

//注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获得连接
			conn = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest","root", "123456");
			//创建执行SQL语句的对象
			stat = (Statement)conn.createStatement();
			//编写SQL语句
			String sql = "update user set username = 'qqq',password='456',name='赵六' where uid = 4";
			//执行SQL语句
			int i = stat.executeUpdate(sql);
			if (i>0) {
				System.out.println("更新成功");
			}

• 删除数据库中的记录

//编写SQL
			String sql = "delete from user where uid = 4";
			//执行SQL语句
			int i = stat.executeUpdate(sql);
			if (i>0) {
				System.out.println("删除成功");
			}

• 查询数据库中的记录

            String sql = "select * from user";
			rSet = stat.executeQuery(sql);
			while(rSet.next()) {
				System.out.println(rSet.getInt("uid") + "   "+ rSet.getString("username")+ "     "+rSet.getString("password")+"     "+rSet.getString("name"));
			}

JDBC的工具类抽取

配置文件jdbc.properties

driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql:///jdbctest
username = root
password = 123456

工具类

package com.imooc.jdbc.utils;
/**
 * JDBC的工具类
 * @author Administrator
 *
 */

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

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class JDBCUtils {
	
	private static final String driverClass;
	private static final String url;
	private static final String username;
	private static final String password;
	
	static {
		//加载属性文件并解析;
		Properties props = new Properties();
		//如何获得属性文件的输入流？
		//通常情况下使用类的加载器的方式进行获取
		InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
		try {
			props.load(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		driverClass = props.getProperty("driverClass");
		url = props.getProperty("url");
		username = props.getProperty("username");
		password = props.getProperty("password");
	}
	
	
	/**
	 * 注册驱动的方法
	 * @throws ClassNotFoundException 
	 */
	public static void loadDriver() throws ClassNotFoundException {
		Class.forName(driverClass);
	}
	
	/**
	 * 获得连接的方法
	 * @throws Exception 
	 */
	public static Connection getConnection() throws Exception {
		loadDriver();
		Connection conn = (Connection)DriverManager.getConnection(url, username, password);
		return conn;
	}
	
	/**
	 * 资源释放
	 */
	public static void release(Statement stmt, Connection conn) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn = null;
		}
	}

	public static void release(Statement stmt, Connection conn,ResultSet rSet) {
		release(stmt, conn);
		if (rSet != null) {
			try {
				rSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			rSet = null;
		}
	}
}