JDBC

JDBC(Java Data Base Connectivity,Java数据库连接)是Java与数据库间的一套接口规范。

1.连接数据库
 获取数据库驱动的方式有三种，一般使用的Class.forname

　DriverManager.deregisterDriver(new com.mysql.jdbc.Driver());

  System.setProperty("jdbc", "com.mysql.jdbc.Driver");

  Class.forName("com.mysql.jdbc.Driver");

2.产生连接对象

Connection conn = DriverManager.getConnection(url,users,password);

3.执行SQL语句

Statement st = conn.createStatement();

String sql = ''select * from tb1';            //结果集装sql产物

4.产生结果集，对结果集进行遍历

ResultSet rs = st.executeQuery(sql);

while(rs.next)){

     Ststem.out.println(rs.getInt(("id")+"\t"+rs.getString("Username")+"\t"+rs.getDouble("Salary"));

}

5.关闭且释放资源

conn.close();

rs.close();

st.close();

6.连接数据库的通用方法

package com.openlab.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 Properties properties = new Properties();
	private static String user = null;
	private static String password = null;
	private static String url = null;

	
	static{
		try {
			
			InputStream is = 
					JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properites");
			properties.load(is);
			String driver = properties.getProperty("driver");
			user = properties.getProperty("user");
			password = properties.getProperty("password");
			url = properties.getProperty("url");
			
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	// 对Connection对象进行封装时 可以选择两种用法  静态方法  单例模式
	/**
	 * 获取connection  方法具有单一性 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException{
		
		return  DriverManager.getConnection(url, user, password);
	}
	
	
	
	public static void close(Connection conn, Statement st, ResultSet rs){
		
		try {
			if(conn != null){
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			try {
				if(st!=null){
					st.close();
					st = null;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				
				try {
					if(rs!=null){
						rs.close();
						rs = null;
					}
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		}
		
		
	}


}

7.单条或多条通用查询方法

/**
	 * 单条的通用查询方法
	 * @param sql
	 * @return
	 */
	public Object queryByOne(String sql){
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		Object obj = null;
		
		try {
			conn = getConnection();
			st =  conn.createStatement();
			rs = st.executeQuery(sql);
			 obj = rsToObj(rs);
			
		} catch (SQLException e) {
			
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(conn, st, rs);
		}
		
		return obj;
		
	}
	
	/**
	 * 通用的多条查询方法
	 * @param sql
	 * @return
	 */
	public List<Object> queryByAll(String sql){
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		List<Object> list = null;
		
		try {
			conn = getConnection();
			st =  conn.createStatement();
			rs = st.executeQuery(sql);
			list = rsToList(rs);
			
		} catch (SQLException e) {
			
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(conn, st, rs);
		}
		
		return list;
		
	}

8.增删改

public class JDBCTest {
 
	
	
	/**
	 * 增加数据
	 */
	public static void add() throws SQLException{
		String sql = "INSERT INTO USER (user_name,user_password,user_age) VALUES('老王','123456',18)";
		Connection conn = DBUtil.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.executeUpdate();
		conn.commit();
		conn.close();
	}
	
	/**
	 * 删除数据
	 */
	public static void delete() throws SQLException{
		String sql = "DELETE FROM USER WHERE USER.user_name = '老王'";
		Connection conn = DBUtil.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.executeUpdate();
		conn.commit();
		conn.close();
	}
	
	/**
	 * 修改数据
	 */
	public static void updata() throws SQLException{
		String sql = "UPDATE USER SET USER.user_name = '老李'";
		Connection conn = DBUtil.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.executeUpdate();
		conn.commit();
		conn.close();
	}

	
}