用了太久的框架,传统的JDBC也不能忘记,以下是传统的JDBC实现连接数据库
使用传统的JDBC实现数据库连接,一般要DIY一个工具类,这个工具类中,最好使用连接池,数据库连接池有很多,JNDI,DBCP,C3P0等皆可以实现,这里我使用的是C3P0连接池。
C3P0连接池的配置文件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:///fruit</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">50</property>
<property name="maxPoolSize">100</property>
</default-config>
<named-config name="namedConfig">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///fruit</property>
<property name="user">root</property>
<property name="password">123456</property>
</named-config>
</c3p0-config>
没有使用C3P0连接池之前,获取数据库连接对象是这样的(这是最原始的):
// 获取数据库连接对象
public static Connection getConnection() {
if (conn != null) {
return conn;
}
try {
// 加载驱动类,不同的数据库软件驱动类不同
Class.forName(MYSQL_DRIVER);
// 使用DriverManager获得连接对象,其中URL每个数据库不同
conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER,
MYSQL_PASS);
System.out.println("数据库已连接...");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
// 返回获得的连接对象
return conn;
}
DIY封装的工具类加上C3P0连接池之后,获取数据库连接对象由C3P0连接池完成。
加上C3P0连接池后,DIY的工具类如下:
package com.fruitsys.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* These are data base utils
* @author LIU
* @version 1.0 2017-09-21
*/
public class BaseDao {
/**
* Create Connection
*/
private static Connection conn = null;
/**
* Create ResultSet
*/
private static ResultSet rs = null;
/**
* Create PreparedStatement
*/
private static PreparedStatement pstmt = null;
/**
* Create ComboPooledDataSource
*/
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
/**
* Get dataSource
*/
public DataSource getDataSource(){
return dataSource;
}
/**
* Get a Connection
*TODO
*LIU
* @return Connection
* pm4:42:19
*/
public Connection getConnection(){
try {
return dataSource.getConnection();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* Update information by sql
*TODO
*LIU
* @param sql
* @param objects
* @return int
* pm4:42:37
*/
public int updateBySql(String sql, Object ...objects){
int result = 0;
conn = getConnection();
try {
pstmt = conn.prepareStatement(sql);
if (objects.length > 0) {
for (int i = 0; i < objects.length; i++) {
pstmt.setObject(i+1, objects[i]);
}
}
result = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* Get information from database by sql
* TODO
* LIU
* @param sql
* @param objects
* @return ResultSet
* pm4:43:22
*/
public ResultSet selectBySql(String sql, Object ...objects){
conn = getConnection();
try {
pstmt = conn.prepareStatement(sql);
if (objects.length > 0) {
for (int i = 0; i < objects.length; i++) {
pstmt.setObject(i+1, objects[i]);
}
}
rs = pstmt.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
/**
* Close Connetion,ResultSet or PreparedStatement
*TODO
*LIU
*pm 11:16:00
*/
public void close() {
try {
if(rs != null){
rs.close();
}
if(pstmt != null){
pstmt.close();
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
在数据访问实现类中使用DIY的工具类,实现对数据的增删改查。
数据访问实现类如下:
package com.fruitsys.dao.impl;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.fruitsys.bean.Fruit;
import com.fruitsys.bean.Page;
import com.fruitsys.dao.BaseDao;
import com.fruitsys.dao.FruitDao;
/**
* 水果信息数据访问实现类
* @author LIU
* @version 1.0 2017-10-17
*/
public class FruitDaoImpl extends BaseDao implements FruitDao {
/**
* 获取总记录数
*TODO
*LIU
* @return
*下午4:06:37
*/
@Override
public int getAllCount() {
// TODO Auto-generated method stub
String sql = "select count(id) from fruit";
try {
ResultSet rs = selectBySql(sql);
if(rs.next()){
return rs.getInt(1);
}
} catch (Exception e) {
// TODO: handle exception
}finally{
close();
}
return 0;
}
/**
* 获取当前页的信息
*TODO
*LIU
* @param page
* @return
*下午4:09:03
*/
@Override
public List<Fruit> showCurrList(Page page) {
// TODO Auto-generated method stub
String sql = "select id,type,breed,area,brief,weight,price from fruit limit ?,?";
List<Fruit> fruitList = new ArrayList<Fruit>();
try {
ResultSet rs = selectBySql(sql, page.getStart(), page.getCurrCount());
while(rs.next()){
Fruit f = new Fruit();
f.setId(rs.getInt("id"));
f.setType(rs.getString("type"));
f.setBreed(rs.getString("breed"));
f.setArea(rs.getString("area"));
f.setBrief(rs.getString("brief"));
f.setWeight(rs.getInt("weight"));
f.setPrice(rs.getDouble("price"));
fruitList.add(f);
}
} catch (Exception e) {
// TODO: handle exception
}finally{
close();
}
return fruitList;
}
/**
* 显示一条信息详情
*TODO
*LIU
* @return
*下午4:10:18
*/
@Override
public Fruit showOne(int id) {
// TODO Auto-generated method stub
String sql = "select id,type,breed,area,brief,weight,price from fruit where id=?";
try {
ResultSet rs = selectBySql(sql, id);
while(rs.next()){
Fruit f = new Fruit();
f.setId(rs.getInt("id"));
f.setType(rs.getString("type"));
f.setBreed(rs.getString("breed"));
f.setArea(rs.getString("area"));
f.setBrief(rs.getString("brief"));
f.setWeight(rs.getInt("weight"));
f.setPrice(rs.getDouble("price"));
return f;
}
} catch (Exception e) {
// TODO: handle exception
}finally{
close();
}
return null;
}
/**
* 删除一条信息
*TODO
*LIU
* @param id
* @return
*下午4:09:48
*/
@Override
public int deleteFruit(int id) {
// TODO Auto-generated method stub
String sql = "delete from fruit where id=?";
int result = updateBySql(sql, id);
return result;
}
}
项目目录结构如下:
感谢您的阅读,欢迎参观我的个人网站:小嗨词典【 https://www.happydict.cn】