java中的JDBC

JDBC概述

6步实现JDBC

package cn.cy.jdbc;

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

import com.mysql.jdbc.Driver;

public class Demo1 {
	public static void main(String[] args) throws SQLException {
		//获取数据库驱动
		DriverManager.registerDriver(new Driver());
		//创建数据库连接
		Connection conn = DriverManager.getConnection(
	"jdbc:mysql://localhost:3306/mydb1", "root", "root");
		//获取传输器
		Statement stat = conn.createStatement();
		//利用传输器传输sql，并获取返回结果
		ResultSet rs = stat.executeQuery("select * from exam");
		//5.遍历结果
		while(rs.next()){
			int id = rs.getInt("id");
			String name = rs.getString(2);
			System.out.println("id:"+id+">>name:"+name);
		}
		//6.关闭资源
		//后创建的先关闭
		rs.close();
		stat.close();
		conn.close();
	}
}

程序详解

• DriverManager
  
• 数据库URL
  
• 常用数据库URL地址的写法
  Oracle写法：jdbc:oracle:thin:@localhost:1521:sid
  SqlServer—jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid
  MySql—jdbc:mysql://localhost:3306/sid
  Mysql的url地址的简写形式： jdbc:mysql:///sid
  常用属性：useUnicode=true&characterEncoding=UTF-8
• 程序详解—Connection
  
• 程序详解—Statement
  
• 程序详解—ResultSet
  
• ResultSet中的api
  

工具类JDBCUtils

package cn.cy.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
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 JDBCUtils(){
		
	}
	public static Properties prop = new Properties();
	static{
		try {
			prop.load(new FileInputStream(new File(
				//获取类加载器，JDBCUtils.class.getClassLoader()
				//通过类加载器获取src目录，getResource()
				//getResource()会得到从盘符到src目录的路径，
				//直接在括号中书写文件名称即可得到文件路径。
				//getPath()是为了将url转换为String类型的数据
				JDBCUtils.class.getClassLoader().getResource("conf.properties").getPath())));
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//创建连接
	public static Connection getConnection() throws Exception{
		Class.forName(prop.getProperty("driver"));
		return DriverManager.getConnection(prop.getProperty("url")
			, prop.getProperty("user"), prop.getProperty("password"));
	}
	//关闭资源
	public static void close(Connection conn,Statement stat,ResultSet rs){
		if(rs !=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}finally{
				rs = null;
			}
		}
		if(stat != null){
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}finally{
				stat = null;
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}finally{
				conn = null;
			}
		}
		
	}
}

批处理

package cn.cy.batch;

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

import cn.tedu.utils.JDBCUtils;

//Statement批处理
/*
 	create table t1(id int,name varchar(20))
  	insert into t1 values(1,'鸣人')
  	insert into t1 values(2,'佐助')
  	insert into t1 values(3,'小樱')
  	insert into t1 values(4,'蝎子')
  	
  Statement特点：
  	优点：1.可以执行不同语义的sql
  	缺点：1.没有预编译功能
  		2.每次都会将sql语句完整的发送到数据库服务器。
  		3.无法预留sql语句在服务器中，执行效率较低。
  		
 
 * */
public class StateBatchDemo1 {
	public static void main(String[] args) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			stat = conn.createStatement();
			stat.addBatch("create table t1(id int,name varchar(20))");
			stat.addBatch("insert into t1 values(1,'鸣人')");
			stat.addBatch("insert into t1 values(2,'佐助')");
			stat.addBatch("insert into t1 values(3,'小樱')");
			stat.addBatch("insert into t1 values(4,'蝎子')");
			//通知数据库服务器
			stat.executeBatch();
			System.out.println("Statement批处理执行成功");
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.close(conn, stat, rs);
		}
	}

}

package cn.cy.batch;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import cn.tedu.utils.JDBCUtils;
//PreparedStatement批处理
/*
 * PreparedStatement特点：
 	优点：1.有预编译功能。
 		2.将sql主干预留在数据库服务器中，不必重复发送sql语句。
 		3.每次仅发送sql参数部分，执行效率较高。
 	缺点：1.只能执行同一语义的sql语句。
 * 
 * */
public class PreparedBatchDemo1 {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement("insert into t1 values(?,?)");
			for(int i=0;i<100000;i++){
				ps.setInt(1, i);
				ps.setString(2, "name"+i);
				ps.addBatch();
				if(i%1000 ==0){
					ps.executeBatch();
					ps.clearBatch();
					System.out.println("执行完毕，当前批次数为："+i/1000);
				}
				
			}
			//循环可能有不满一千的数据，通过本句来执行。
			ps.executeBatch();
			System.out.println("PreparedStatement执行完毕");
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.close(conn, ps, rs);
		}
	}
}

连接池

			package cn.tedu.pool;
			
			import java.io.File;
			import java.io.FileInputStream;
			import java.sql.Connection;
			import java.sql.ResultSet;
			import java.sql.SQLException;
			import java.sql.Statement;
			import java.util.Properties;
			
			import javax.sql.DataSource;
			
			import org.apache.commons.dbcp.BasicDataSource;
			import org.apache.commons.dbcp.BasicDataSourceFactory;
			
			import cn.tedu.utils.JDBCUtils;
			
			//DBCP连接池测试使用
			public class DBCPDemo1 {
			        public static void main(String[] args) {
			                Connection conn = null;
			                Statement stat = null;
			                ResultSet rs =  null;
			                BasicDataSource source = new BasicDataSource();
			                source.setDriverClassName("com.mysql.jdbc.Driver");
			                source.setUrl("jdbc:mysql://localhost:3306/mydb1");
			                source.setUsername("root");
			                source.setPassword("root");
			                //利用工厂生产一个DBCP数据源对象
			                try {
			                        /*Properties prop = new Properties();
			                        prop.load(new FileInputStream(new File(DBCPDemo1.class.getClassLoader().getResource("dbcp.properties").getPath())));*/
			                        /*BasicDataSourceFactory factory = new BasicDataSourceFactory();
			                        DataSource source = factory.createDataSource(prop);*/
			                        conn = source.getConnection();
			                        stat = conn.createStatement();
			                        rs = stat.executeQuery("select * from exam");
			                        while(rs.next()){
			                                int id = rs.getInt(1);
			                                String name = rs.getString(2);
			                                System.out.println("id:"+id+">>name:"+name);
			                        }
			                } catch (Exception e) {
			                        throw new RuntimeException(e);
			                }finally{
			                        if(rs !=null){
			                                try {
			                                        rs.close();
			                                } catch (SQLException e) {
			                                        e.printStackTrace();
			                                        throw new RuntimeException(e);
			                                }finally{
			                                        rs = null;
			                                }
			                        }
			                        if(stat != null){
			                                try {
			                                        stat.close();
			                                } catch (SQLException e) {
			                                        e.printStackTrace();
			                                        throw new RuntimeException(e);
			                                }finally{
			                                        stat = null;
			                                }
			                        }
			                        if(conn != null){
			                                try {
			                                        //归还连接
			                                        conn.close();
			                                } catch (SQLException e) {
			                                        e.printStackTrace();
			                                        throw new RuntimeException(e);
			                                }finally{
			                                        conn = null;
			                                }
			                        }
			                }
			        }
			}

C3P0

c3p0连接池会自动读取，位于当前工程下src目录中的c3p0-config.xml文件或c3p0.properties文件。

			package cn.cy.pool;
			
			import java.beans.PropertyVetoException;
			import java.sql.Connection;
			import java.sql.ResultSet;
			import java.sql.SQLException;
			import java.sql.Statement;
			
			import com.mchange.v2.c3p0.ComboPooledDataSource;
			
			//C3P0连接池测试使用
			public class C3P0Demo1 {
			        public static void main(String[] args) {
			                Connection conn  = null;
			                Statement stat = null;
			                ResultSet rs = null;
			                
			                ComboPooledDataSource source = new ComboPooledDataSource();
			                try {
			                        /*source.setDriverClass("com.mysql.jdbc.Driver");
			                        source.setJdbcUrl("jdbc:mysql://localhost:3306/mydb1");
			                        source.setUser("root");
			                        source.setPassword("root");*/
			                        conn = source.getConnection();
			                        stat = conn.createStatement();
			                        rs = stat.executeQuery("select * from exam");
			                        while(rs.next()){
			                                String name = rs.getString("name");
			                                System.out.println("name:"+name);
			                        }
			                } catch (Exception e) {
			                        e.printStackTrace();
			                }finally{
			                        if(rs !=null){
			                                try {
			                                        rs.close();
			                                } catch (SQLException e) {
			                                        e.printStackTrace();
			                                        throw new RuntimeException(e);
			                                }finally{
			                                        rs = null;
			                                }
			                        }
			                        if(stat != null){
			                                try {
			                                        stat.close();
			                                } catch (SQLException e) {
			                                        e.printStackTrace();
			                                        throw new RuntimeException(e);
			                                }finally{
			                                        stat = null;
			                                }
			                        }
			                        if(conn != null){
			                                try {
			                                        //归还连接
			                                        conn.close();
			                                } catch (SQLException e) {
			                                        e.printStackTrace();
			                                        throw new RuntimeException(e);
			                                }finally{
			                                        conn = null;
			                                }
			                        }
			                }
			        }
			}