09月
16

发表于 • 字数统计 8804 • 被 418 人看爆

JDBC

步骤

  1. 注册驱动/加载驱动
Class.forName("com.mysql.jdbc.Driver");
  1. 获取连接对象
// getConnection(url jdbc:mysql://[hostname]:[port]/[dbname]/[encode_format])
Connection conn = null;	//需要在try块外声明,否则在finally里无法关闭
//获取连接器对象
conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookmanagement?useUnicode=true&charaterEncoding=utf8","root","root");

注：导包导的是java.sql.Connection、java.sql.DriverManager

  1. 获取sql执行器对象Statement/PreparedStatement

Statement后期不会常用，有sql注入的风险

Statement写法

Statement st = null;	//需要在try块外声明,否则在finally里无法关闭
//下面代码需要放在try块里
st = conn.createStatement();

PreparedStatement写法

PreparedStatement pst = null;
  1. 执行并返回处理结果

增、删、改、返回的受影响的行数 int整数值executeUpdate()查询，返回的结果及对象ResultSet对象，返回的是一个集合executeQuery()

Statement后期不会常用，有sql注入的风险
Statement写法

ResultSet rs = null;	//需要在try块外声明,否则在finally里无法关闭
//下面代码需要放在try块里
String sql = "select * from bookmanager";
rs = st.executeQuery(sql);
while (rs.next){
	System.out.println(rs.getInt("t_stuNo"+"\t"+rs.getString("t_stuName")
                       +"\t"+rs.getInt("t_classId")+"\t"+rs.getInt("t_score"));
}

PreparedStatement写法


//获取sql执行器对象
String sql = "insert into t_user(t_userName,t_pwd) values(?,?)";
pst = conn.preparedStatement(sql);
//设置占位符
pst.setString(1,username);
pst.setString(2,pwd);
//执行并返回处理结果
rs = pst.executeQuery();
  1. 释放资源

关闭顺序1: ResultSet rs ---> Statement/PreparedStatement ---> Connection（查询的关闭）

关闭顺序2: Statement/PreparedStatement ---> Connection (增，删，改的关闭)

// 每一个需要异常捕获
if (rs != null){
	rs.close();
}
if (st != null){
	st.close();
}
if (conn != null){
	conn.close();
}

if (rs != null){
	rs.close();
}
if (pst != null){
	pst.close();
}
if (conn != null){
	conn.close();
}

JdbcUtil

用到了两个连接池相关的jar包

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
            <version>2.9.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.8.0</version>
        </dependency>

需要定义下面两个属性：

  • 属性1 ： DataSource ds = null
  • 属性2 ： ThreadLocal threadLocal = new ThreadLocal();
  1. 静态代码块中（加载配置文件、从连接池中拿数据源对象，
  2. 获取连接对象并存储在线程里
  3. 关闭连接对象，移除线程
private static DataSource ds = null;

private static ThreadLocal threadLocal = new ThreadLocal();

static {
    Properties p = new Properties();
    p.load(JdbcUtil.class.getClassLoader.getResourceAsStream("datasource.properties"));

    ds = BasicDataSourceFactory.createDataSource(p);
}

public static Connection getConnection(){
    Connection conn = threadLocal.get();
    if(conn == null){
        conn = ds.getConnection();
    	threadLocal.set(conn);
    }
    return threadLocal.get();
}
public static void close(){
    Connection conn = threadLocal.get();
    if (conn != null){
        conn.close();
	threadLocal.remove();
    }
}

public static void close(ResultSet rs,PreparedStatement pst){
    if (rs != null){
        rs.close();
    }
    if (pst != null){
        pst.close();
    }
}

JdbcTemplate

  • 这个类主要有三个方法：
  1. 设置sql语句中的参数 主要参数pst 和 Object...params
  2. 实现增、删、改，即 executeUpdate() 主要参数 sql 、Object...params
  3. 实现查，即executeQuery() 主要参数 sql 、RowMapper mapper、Object...params
public class JdbcTemplate{
	//1.设置占位符
	private static void setParams(PreparedStatement pst,Object...params) throws SQLException{
		if (params!=null && params.length>0){
			for (int i = 0;i < params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
	}
	//增、删、改
	public static int executeUpdate(String sql,Object..params) throws SQLException{
		int count = 0;
		//获取连接对象
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		try {
			//获取SQL执行器
			pst = conn.perparedStatement(sql);
			
			setParams(pst,params);
			count = pst.executeUpdate();
		} catch (SQLException e){
			e.printStackTrace();
			throw e;
		}finally{
			JdbcUtil.close(null,pst);
		}
	}
	public static List executeQuery(String sql,RowMapper mapper,object..params) throws SQLException{
		List list = new List();
		//获取连接对象
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			//获取SQL执行器
			pst = conn.perparedStatement(sql);
			
			setParams(pst,params);
			rs = pst.executeQuery();
			while(rs.next()){
				Object obj = mapper.mapperObj(rs);
				list.add(obj);
			}
		} catch (SQLException e){
			e.printStackTrace();
			throw e;
		}finally{
			JdbcUtil.close(rs,pst);
		}
		return list;
	}
}

Druid连接池使用

依赖包

druid-1.2.6.jar

测试类

public class DruidDemo {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
//			conn = JdbcUtils.getConnection();

			/*
			 * Method 1
			DruidDataSource dataSource = new DruidDataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUsername("root");
			dataSource.setPassword("password");
			dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test1?useSSL=false&useUnicode=true&CharactorEncoding=utf8");
			conn = dataSource.getConnection();
			 */

			/*
			 * Method 2
			Properties p = new Properties();
			p.load(DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"));
			DataSource dataSource = DruidDataSourceFactory.createDataSource(p);
			conn = dataSource.getConnection();
			 */

			/*
			 * Method 3
			 */
			conn = DruidUtils.getConnection();

			String sql = "select * from account";
			pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				System.out.println("Username: "+ rs.getString("name") + " , Balance: " + rs.getFloat("balance"));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
}

	private static Properties prop = new Properties();
	private static DataSource dataSource = null;
	static {
		try {
			/*
			 * Method 3
			 */
			prop.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
			dataSource = DruidDataSourceFactory.createDataSource(prop);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	/*
	 * 获得连接
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}


	/*
	 * 释放资源
	 */

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

	public static void close(ResultSet rs, Connection conn) {
		if (rs != null) {
			try {
				rs.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();
			}
		}
	}

	public static void close(PreparedStatement pst, ResultSet rs, Connection conn) {
		if (pst != null) {
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.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();
			}
		}
	}

druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test1?useSSL=false&useUnicode=true&CharactorEncoding=utf8
username=root
password=password

C3P0连接池使用

依赖包

mchange-commons-java-0.2.20.jar
c3p0-0.9.5.5.jar

使用方式一：

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test1?useSSL=false&useUnicode=true&CharactorEncoding=utf8");
cpds.setUser("root");
cpds.setPassword("root");
conn = cpds.getConnection();

使用方式二(配置文件)：

无需手动指定配置文件，会自动扫描ClassPath下的c3p0-config.xml配置文件

ComboPooledDataSource cpds = new ComboPooledDataSource();
conn = cpds.getConnection();

// 配置指定数据源
ComboPooledDataSource cpds = new ComboPooledDataSource("mysql-1");
conn = cpds.getConnection();
  • 编写配置文件

详细可参考:https://www.mchange.com/projects/c3p0/#configuration_files

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test1?useSSL=false</property>
    <property name="user">root</property>
    <property name="password">root</property>
  </default-config>
  <named-config name="mysql-1"> 
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test1?useSSL=false</property>
    <property name="user">root</property>
    <property name="password">root</property>
  </named-config>
</c3p0-config>

DbUtils工具类使用

分享到：