- 创建连接对象
错误：com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
在使用properties配置文件连接数据库时报错，url填写错误。
最终配置：
public class ConnectionFinal
{
@Test
public void demo() throws Exception {
InputStream in = ConnectionFinal.class.getClassLoader().getResourceAsStream( "jdbc.properties" );
//void load(InputStream inStream) 从输入字节流读取属性列表（键和元素对）。
Properties p=new Properties( );
p.load( in );
String user = p.getProperty( "user" );
String url = p.getProperty( "url" );
String password = p.getProperty( "password" );
String driver = p.getProperty( "driver" );
Class.forName( driver);
System.out.println(DriverManager.getConnection( url,user,password ));
}
}
**
2. preparedStamtement来实现CRUD操作**
用一个jdbcutil类将连接数据库获取连接封装起来，用update2方法可以写一个通用的增删改的操作将，直接传入sql语句就可以调用
/*可以通用的增删改的操作*/
@Test
public static void update2(String sql,Object...args) {
Connection con=null;
PreparedStatement ps=null;
try {
//1获得数据库的连接
con = jdbcUtil.getConnection();
//2预编译sql语句
ps = null;
ps = con.prepareStatement( sql );
//3填充占位符
for (int i=0;i<args.length;i++){
ps.setObject( i+1,args[i] );
}
//4执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//5关闭资源
jdbcUtil.closeConnection( con,ps );
}
;
}
//调用这里面的方法
public static void main(String[] args) {
//增加
// String sql="insert into customers(name)value(?)";
// update(sql,"增加");
// String sql="delete from customers where id=?";
// update(sql,"1");
String sql="update customers set name =? where id=?";
update2(sql,"2","3");
}
}
**工具类jdbutil**
//jdbc连接的数据包
public class jdbcUtil {
//获取连接
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream( "jdbc.properties" );
Properties ps=new Properties( );
ps.load( is );
String url = ps.getProperty( "url" );
String password = ps.getProperty( "password" );
String user = ps.getProperty( "user" );
String driver = ps.getProperty( "driver" );
Class.forName( driver );
Connection con = DriverManager.getConnection( url, user, password );
return con;
}
//关闭连接
public static void closeConnection(Connection con, Statement st) {
try {
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {if (st!=null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3.用preparedStatement来进行表的查询
首先创建一个对象用与返回结果
对于一个表的通用格式
public static Customer demo1(String sql ,Object...args){
Connection con = null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
//1获取链接
con = jdbcUtil.getConnection();
//2预编译SQL语句
ps = con.prepareStatement( sql );
//3填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject( i + 1, args[i] );
}
//4执行语句，返回到了一个结果集
rs = ps.executeQuery();
//要将结果接用一个对象返回回来
//5获得结果记得元数据，以此来获得列的个数
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//将获得的数据放到对应的列明字段中，生成一个对象，查询的结果可以用对象输出出来
if (rs.next()) {
Customer cust = new Customer();
for (int i = 0; i < columnCount; i++) {
// Object getObject(int columnIndex, Map<String,Class<?>> map) 以Java编程语言中的 Object此 ResultSet对象的当前行中指定列的值。
Object value = rs.getObject( i + 1 );
//获取每个列的列名与对象中的属性一致 结果集也是充一开始
String columnName = rsmd.getColumnName( i + 1 );
//通过反射将属性的值赋值给对象的属性
//根据名字columnName获取了对应的成员变量对象filed，在调用set方法进行
Field field = Customer.class.getDeclaredField( columnName );
//暴力反射
// set(Object obj, Object value) 将指定的对象参数中由此 Field对象表示的字段设置为指定的新值。
field.setAccessible( true );
field.set( cust, value );
}
return cust;
}
}catch (Exception e) {
e.printStackTrace();
}finally {
//关闭资源
jdbcUtil.closeConnection2( con,ps,rs );
}
return null; }
// 调用测试
public static void main(String[] args){
String sql="select id,name,email,birth from customers where id=?";
Customer cust = demo1( sql, 6 );
System.out.println(cust);
}
可以用于各个表的select查询返回一个结果
/*
* 一个可以通用的查询
* */
public static <T> T selectFinal(Class<T> clazz,String sql,Object...args){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
//1确定连接
conn = jdbcUtil.getConnection();
ps= conn.prepareStatement( sql );
//填充占位符
for (int i=0;i<args.length;i++){
ps.setObject( i+1,args[i] );
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
//获得列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
//可以获列值
Object value = rs.getObject( i + 1 );
//可以读取到别名
String columnName = rsmd.getColumnLabel( i + 1 );
Field field = clazz.getDeclaredField( columnName );
field.setAccessible( true );
field.set( t, value );
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
jdbcUtil.closeConnection2( conn,ps,rs );
}
return null;
}
用于各个表，返回多条结果list
/*用于多个返回结构的查询*/
public static <T> List<T> selectFinal2(Class<T> clazz, String sql, Object...args){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
//1确定连接
conn = jdbcUtil.getConnection();
ps= conn.prepareStatement( sql );
//填充占位符
for (int i=0;i<args.length;i++){
ps.setObject( i+1,args[i] );
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
//获得列数
int columnCount = rsmd.getColumnCount();
ArrayList<T> List = new ArrayList<T>(); //创建存储对象集合
while (rs.next()) { //if是只进行一次判断，while时进行多次判断
T t = clazz.newInstance();
//在每一个对象里面存放值
for (int i = 0; i < columnCount; i++) {
//可以获列值
Object value = rs.getObject( i + 1 );
//可以读取到别名
String columnName = rsmd.getColumnLabel( i + 1 );
Field field = clazz.getDeclaredField( columnName );
field.setAccessible( true );
field.set( t, value );
}
List.add( t );
}
return List;
}catch (Exception e){
e.printStackTrace();
}finally {
jdbcUtil.closeConnection2( conn,ps,rs );
}
return null;
}
数据库的事务处理
在考虑事务处理后的增删改查
public class CRUDTestTX {
@Test
//测试类
public static void main(String[] args) {
Connection con=null;
try {
con = jdbcUtils.getConnection();
con.setAutoCommit( false );
String sql="update user_table set balance=balance-100 where user=?";
update(con,sql,"AA");
//模拟网络中断
System.out.println(100/0);
String sql2="update user_table set balance=balance+100 where user=?";
update(con,sql2,"BB");
//没有异常提交数据
con.commit();
} catch (Exception e){
e.printStackTrace();
//有异常数据回滚
try {
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
//恢复数据自动提交功能
try {
con.setAutoCommit( true );
} catch (SQLException e) {
e.printStackTrace();
}
jdbcUtils.closeConnection( con,null);
}
}
//在考虑数据库事务下进行的增删改
@Test
public static void update( Connection con,String sql,Object...args) {
PreparedStatement ps=null;
try {
ps = con.prepareStatement( sql );
//填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject( i + 1, args[i] );
}
ps.execute();
}catch (Exception e) {
e.printStackTrace();
}finally {
jdbcUtils.closeConnection(null,ps);
}
}
}
DAO类
BaseDao是一个数据库的基本操作的类
Customer是对于该表的操作的方法的规范
CustomersDaoImpl是继承了BaseDao,实现了CustomersDao的接口
BaseDao
package dao2;
import dao.jdbcUtils;
import java.lang.reflect.AnnotatedType;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/*
* 这个类作为操作数据库的基本的类,，无法生成对象，只可以被继承*/
public abstract class BaseDao<T> {
private Class<T> clazz=null;
//获取父类的泛型
{
Type type = this.getClass().getGenericSuperclass();
ParameterizedType type1 = (ParameterizedType) type;
Type[] types = type1.getActualTypeArguments();//获取父类的泛型参数
clazz= (Class<T>) types[0]; //获取了父类的泛型
}
/*增删改的通用操作，在考虑过事务之后*/
public int update(Connection con, String sql, Object... args) {
PreparedStatement ps = null;
try {
ps = con.prepareStatement( sql );
for (int i = 0; i < args.length; i++) {
ps.setObject( i + 1, args[i] );
}
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
jdbcUtils.closeConnection( null, ps );
}
return 0;
}
/*查询某一个信息*/
public T findUser(Connection con,String sql,Object...args){
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = con.prepareStatement( sql );
//填充占位符
for (int i=0;i<args.length;i++){
ps.setObject( i+1,args[i] );
}
//获得结果集
rs=ps.executeQuery();
//获得元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获得列数
int columnCount = rsmd.getColumnCount();
if (rs.next()){
//创建对象
T t = clazz.newInstance();
for (int i=0;i<columnCount;i++){
Object value = rs.getObject( i+1 );
String columnLabel = rsmd.getColumnLabel( i+1 );
Field field = clazz.getDeclaredField( columnLabel );
field.setAccessible( true );
field.set( t,value );
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
jdbcUtils.closeConnection( null,ps,rs );
}
return null;
}
/*返回多个对象*/
public List<T> findMany(Connection con,String sql){
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=con.prepareStatement( sql );
rs=ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
ArrayList<T> list = new ArrayList<T>( );
while (rs.next()){
T t = clazz.newInstance();
for (int i=0;i<columnCount;i++){
Object value = rs.getObject( i+1 );
String columnLabel = rsmd.getColumnLabel( i+1 );
Field field = clazz.getDeclaredField( columnLabel );
field.setAccessible( true );
field.set( t,value );
}
list.add( t );
}
return list;
}catch (Exception e){
e.printStackTrace();
}finally {
jdbcUtils.closeConnection( null,ps,rs );
}
return null;
}
/*寻找固定的值*/
public <E> E getValue(Connection con,String sql,Object...args){
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps = con.prepareStatement( sql );
for (int i=0;i<args.length;i++){
ps.setObject( i+1,args[i] );
}
rs = ps.executeQuery();
if (rs.next()){
return (E) rs.getObject( 1);
}
}catch (Exception e){
e.printStackTrace();
}finally {
jdbcUtils.closeConnection( null,ps,rs );
}
return null;
}
}
CustomerDao：一个接口
package dao2;
import connect.selsect.Customer;
import java.sql.Connection;
import java.util.List;
public interface CustomerDao {
//将指定用户添加到数据库中
void insertUaser(Connection con, Customer cust);
//将指定id的用户删除
void deleteUser(Connection con, int id);
//修改
void updateUser(Connection con, Customer cust);
//查询所有
List<Customer> findAll(Connection con);
//查询指定信息
Customer findOne(Connection con, int id);
}
CustomerDaoImpl
package dao2;
import connect.selsect.Customer;
import java.sql.Connection;
import java.util.List;
public class CustomerDaoImpl extends BaseDao<Customer> implements CustomerDao {
@Override
public void insertUaser(Connection con, Customer cust) {
String sql = "insert into customers(id,name,email,birth) values(?,?,?,?)";
update( con, sql, cust.getId(), cust.getName(), cust.getEmail(), cust.getBirth() );
}
@Override
public void deleteUser(Connection con, int id) {
String sql = "delete from customers where id=?";
update( con, sql, id );
}
@Override
public void updateUser(Connection con, Customer cust) {
String sql = "update customers set name=?,email=?,birth=? where id=?";
update( con, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId() );
}
@Override
public List<Customer> findAll(Connection con) {
String sql = "select id,name,email,birth from customers";
List<Customer> list = findMany( con, sql );
return list;
}
@Override
public Customer findOne(Connection con, int id) {
String sql = "select name,email,birth from customers where id=?";
Customer cust = findUser( con, sql, id );
return cust;
}
}
获取父类中的方法
private Class<T> clazz=null;
//获取父类的泛型
{
Type type = this.getClass().getGenericSuperclass();//获得父类泛型
ParameterizedType type1 = (ParameterizedType) type; //进行强转
Type[] types = type1.getActualTypeArguments();//获取父类的泛型参数
clazz= (Class<T>) types[0]; //获取了父类的泛型
}
/* 改代码块写在父类BaseDao中，子类通过继承可以运用，this指的是子类
子类获取父类的泛型参数，获得父类的泛型，也可以写在构造方法中
//定义一个变量来接收泛型的类型
privateClass<T>type;
//获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
publicBaseDao(){
//获取子类的类型
Classclazz=this.getClass();
//获取父类的类型
//getGenericSuperclass()用来获取当前类的父类的类型
//ParameterizedType表示的是带泛型的类型
ParameterizedTypeparameterizedType=(ParameterizedType)
clazz.getGenericSuperclass();
//获取具体的泛型类型getActualTypeArguments获取具体的泛型的类型
//这个方法会返回一个Type的数组
Type[]types=parameterizedType.getActualTypeArguments();
//获取具体的泛型的类型·
this.type=(Class<T>)types[0];
}
*/
数据库连接池之C3P0
配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<!-- This app is massive! -->
<!--获取连接数据库的四个基本信息-->
<!--要与前面的区分大小写，注意大小否则会找不到-->
<named-config name="hhh">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">123456</property>
<!--数据库连接池进行管理-->
<!--当数据库连接池的连接数不够时，c3p0一次性向服务器请求连接的数-->
<property name="acquireIncrement">5</property>
<!--c3p0连接池中初始化的连接数-->
<property name="initialPoolSize">10</property>
<!--c3p0连接池中维护的最小连接数-->
<property name="minPoolSize">15</property>
<!--c3p0连接池中维护的最多连接数-->
<property name="maxPoolSize">50</property>
<!-- intergalactoApp adopts a different approach to configuring statement caching -->
<!--c3p0连接池中可以维护的最多的Statement-->
<property name="maxStatements">50</property>
<!--c3p0连接池中每个连接可以维持的最多的Statement-->
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
注意：1在写四个基本的配置文件是要注意大小写的区分
2.在配置信息要与文件给的向一致
3要另外起一个名字读取自定义的配置文件，配置文件方到src目录下
连接池获取连接
package connection.C3P0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
public class C3p0Test {
//c3po连接池获取连接
@Test
public void getConnectio() throws Exception {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
cpds.setUser("root");
cpds.setPassword("123456");
Connection con = cpds.getConnection();
System.out.println(con);
//通过设置相关参数进行管理
cpds.setInitialPoolSize( 10 );
}
//方式二：可以通过读取配置文件进行
@Test
public void getConnection2() throws SQLException {
ComboPooledDataSource cpds = new ComboPooledDataSource( "hhh" );
Connection con = cpds.getConnection();
System.out.println(con);
}
}
数据库连接池之DBCP
数据库连接池的创建
public class DbcpTest {
@Test
//第一种连接方式
public void getConnection1() throws SQLException {
//创建数据连接池
BasicDataSource source = new BasicDataSource();
//设置四个连接信息
source.setDriverClassName( "com.mysql.jdbc.Driver" );
source.setUrl( "jdbc:mysql:///test" );
source.setUsername( "root" );
source.setPassword( "123456" );
//设置连接池
source.setInitialSize(10);
//获取连接
Connection con = source.getConnection();
System.out.println(con);
}
//第二种连接方式 通过配置文件properties
@Test
public void getConnection2() throws Exception {
Properties p = new Properties();
//获取留的第一种方式
// InputStream is = DbcpTest.class.getClassLoader().getResourceAsStream( "dbcp.properties" );
//获取流的第二种方式
FileInputStream is= new FileInputStream(new File( "src/dbcp.properties" ));
p.load( is );
//调用连接池工厂方法，创建连接池
DataSource source = BasicDataSourceFactory.createDataSource( p );
Connection con = source.getConnection();
System.out.println(con);
}
}
配置文件在src上
dbcp.preporties
username=root
password=123456
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
数据库连接池之Druid
public class DruidTest {
@Test
public void getConnection() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream( "druid.properties" );
Properties p = new Properties();
p.load( is );
DataSource source = DruidDataSourceFactory.createDataSource( p );
Connection connection = source.getConnection();
System.out.println(connection);
}
}
配置文件 druid.properties
name=root
password=123456
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
用Dbutils进行增删改查
public class DButilsTest {
/*用DButils来进行增删改查操作*/
@Test
public void updates() throws SQLException, IOException, ClassNotFoundException {
QueryRunner runner = new QueryRunner();
String sql="insert into customers(id,name,email,birth) values(?,?,?,?)";
Connection con = jdbcUtils.getConnection();
int i = runner.update( con, sql, 20, "sss", "saaa@123.com", "2000-02-22" );
System.out.println(i);
}
/* 查询
BeanHandler 是result
* */
@Test
public void find() throws Exception {
QueryRunner runner = new QueryRunner();
Connection connection3 = jdbcUtils.getConnection3();
BeanHandler<Customer> handler=new BeanHandler<>( Customer.class );
String sql="select name from customers where id=?";
Customer customer = runner.query( connection3, sql, handler, 12 );
System.out.println(customer);
}
@Test
public void findAll() throws Exception {
QueryRunner runner = new QueryRunner();
Connection connection3 = jdbcUtils.getConnection3();
BeanListHandler<Customer> handler=new BeanListHandler<>( Customer.class );
String sql="select name from customers where id<?";
List<Customer> list = runner.query( connection3, sql, handler, 12 );
System.out.println(list);
}
@Test
//使用特殊字符
public void find1() throws Exception {
QueryRunner runner = new QueryRunner();
Connection connection3 = jdbcUtils.getConnection3();
ScalarHandler handler = new ScalarHandler();
String sql="select count(*) from customers";
Object query = runner.query( connection3, sql, handler );
System.out.println(query);
DbUtils.close(connection3 );
}
}
103
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
