本文介绍了Java中JDBC工具类的编写，详细阐述了如何处理资源路径问题，以及c3p0和Druid两种数据库连接池的配置与使用。同时，提到了Spring的JdbcTemplate对象在数据库操作中的应用，包括DML和DQL方法的调用。通过这些内容，读者可以了解Java数据库连接的基础及优化策略。
JDBC java dataBase Connectivity
JDBC java dataBase Connectivity
一个JDBC的自己写的工具类
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
//用类加载器获取资源文件的绝对路径
URL resource = classLoader.getResource(“jdbc.properties”);
/****
*** 这里,因为ClassLoader.getResource()返回的这个绝对路径,是不会对你的路径编码的,如果有空格是哈的,你会直接返回**
*** 此时,如果是有特殊字符,后面的getPath()就会有依据utf-8修改;使得路径找不到**
* 返回错误:jdbc.properties 系统找不到指定路径问题**
*/
URI uri = resource.toURI();
String path = uri.getPath();
package cn.itcast.util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String useer;
private static String password;
private static String driver;
static{
//读取资源文件,不用reader等读取文件的方法
Properties pro = new Properties();
try {
//
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
//用类加载器获取资源文件的绝对路径
URL resource = classLoader.getResource("jdbc.properties");
/**
* 这里,因为ClassLoader.getResource()返回的这个绝对路径,是不会对你的路径编码的,如果有空格是哈的,你会直接返回
* 此时,如果是有特殊字符,后面的getPath()就会有依据utf-8修改;使得路径找不到
* 返回错误:jdbc.properties 系统找不到指定路径问题
*/
URI uri = resource.toURI();
String path = uri.getPath();
System.out.println(path);
//pro.load( new FileReader("D:\\D Computer sth for download\\chuanzhiboke\\itcast\\day04_jdbc\\src/jdbc.properties"));
pro.load(new FileReader(path));
} catch (IOException e) {
e.printStackTrace();
} catch (URISyntaxException e) {
e.printStackTrace();
}
//读取
url = pro.getProperty("url");
useer = pro.getProperty("useer");
password = pro.getProperty( "password");
driver = pro.getProperty("driver");
//注册驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @param url
* @param useer
* @param password
* @return Connection
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,useer,password);
}
/**
* 关闭资源
* @param statement
* @param connection
*/
public void close(ResultSet resultSet,Statement statement, Connection connection){
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection !=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
数据库池
1:c3p0,
2:Druid(德鲁德)数据库连接池实现技术,阿里搞的,
3实现
- 标准接口, DataSource ,java.sql包下的.实现类是厂家写的,
- 获取,getConnection();
- 归还connection.close();
4c3p0
DataSource ds = new ComboPooledDataSource();
Connection connection = ds.getConnection();
-
配置文件从c3p0-config.xml
-
在应用程序的类路径的顶层以标准名称（
c3p0.properties或c3p0-config.xml）查找配置文件 -
名字不能随意更改,也有properties的,
-
<c3p0-config> <default-config> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost/db3</property> <property name="user">root</property> <property name="password">root</property> <property name="initialPoolSize">10</property> <property name="maxIdleTime">30</property> <property name="maxPoolSize">100</property> <property name="minPoolSize">10</property> <property name="maxStatements">200</property> </default-config> <!-- This app is massive! --> <named-config name="intergalactoApp"> <property name="acquireIncrement">50</property> <property name="initialPoolSize">100</property> <property name="minPoolSize">50</property> <property name="maxPoolSize">1000</property> <!-- intergalactoApp adopts a different approach to configuring statement caching --> <property name="maxStatements">0</property> <property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him --> <user-overrides user="master-of-the-universe"> <property name="acquireIncrement">1</property> <property name="initialPoolSize">1</property> <property name="minPoolSize">1</property> <property name="maxPoolSize">5</property> <property name="maxStatementsPerConnection">50</property> </user-overrides> </named-config> </c3p0-config>
5:Druid(德鲁德)
-
配置文件:
-
driverClassName=com.mysql.cj.jdbc.Driver ## url=jdbc:mysql://127.0.0.1:3306/db3 ##这里要写对啊,username username =root password=root initialSize=5 maxAcive=10 maxWait=300 -
是Properties的形式,可以叫任意名称,可以放在任意目录下,#是注释说明文件类型,
-
创建池:
- DataSOurce data = DruidDataSourceFactory.createDataSOurce(pro(properties的一个类));
Druid的一个工具类(简写)
package itcast.utils; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; //Duid 工具类 public class JDBCUtils { // 数据源 private static DataSource dataSource; static{ Properties properties = new Properties(); InputStream resourceAsStream = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"); try { properties.load(resourceAsStream); dataSource = DruidDataSourceFactory.createDataSource(properties); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } //获取连接的方法 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } public static DataSource getDatasource(){ return dataSource; } public static void close(ResultSet resultSet, PreparedStatement statement, Connection connection) { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection != null) try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void close2(PreparedStatement statement, Connection connection) { close(null, statement, connection); } }
6 Spring JDBC-------- JDBCTemplate对象
JDBCTemplate对象,依赖于数据源DataSource;
-
JdbcTempalte jdbcte = new JdbTemplate(Datasource的一个类);
-
调用它的方法
-
DML方法 update(),----增删改
-
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDatasource()); String sql = "update accounts set balance =500 where id =?"; System.out.println(jdbcTemplate.update(sql, 1));
-
-
和DQL方法,dequry… —查
-
public void test2(){ String sql = "select * from accounts where id = ?"; // .queryForMap()只能是一条记录; Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql, 1); System.out.println(stringObjectMap); }
-
-
两条记录就用queryForList();
-
@Test public void test3() { String sql = "select * from accounts "; List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql); System.out.println(maps); }
-
-
@Test
public void test4() {
//自己写的数据类型转换
String sql = "select * from accounts ";
List<Emp> list = jdbcTemplate.query(sql, new RowMapper<Emp>() {
@Override
public Emp mapRow(ResultSet resultSet, int i) throws SQLException {
Emp emp = new Emp();
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
emp.setId(id);
emp.setName(name);
return emp;
}
});
for (Emp i :list
) {
System.out.println(i);
}
}
@Test
//使用厂家自己实现的方法mapRow()的实现类ew BeanPropertyRowMapper<Emp>(Emp.class),不用再重写new RowMapper了.
//工作中常用javaBean这种类型;
public void test5() {
String sql = "select * from accounts ";
//RowMapper<Emp>()接口的一个实现类是:new BeanPropertyRowMapper<>();()给字节码类型
List<Emp> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp i : query
) {
System.out.println(i);
}
}
@Test
public void test6(){
String sql = "select count(id) from accounts";
//一般是聚合函数的查询
System.out.println(jdbcTemplate.queryForObject(sql, Integer.class));
}
扫一扫
299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
