一、jdbc连接数据库
jdbcUtil:
public class JdbcUtil {
private static String url = "jdbc:oracle:thin:@127.0.0.1:15211:orcl";
private static String user = "root";
private static String password = "root";
private static String driver = "oracle.jdbc.OracleDriver";
// 连接数据库
public static Connection getConnection() {
Connection con = null;
try {
// 初始化驱动
Class.forName(driver);
// 获取数据库连接
con = DriverManager.getConnection(url, user, password);
System.out.println("数据库初始化成功!");
} catch (Exception e) {
System.out.println("数据库初始化失败!");
}
return con;
}
// 关闭数据库连接
public static void conClose(Connection con, PreparedStatement st,
ResultSet rs) {
try {
if (con != null) {
con.close();
}
if (st != null) {
con.close();
}
if (rs != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
基于jdbc的crud:
// 保存
public static boolean save(User user) {
Connection con = JdbcUtil.getConnection();
String sql = "insert into Tuser(id,username,password) values(?,?,?)";
PreparedStatement ps = null;
int n = 0;
try {
ps = con.prepareStatement(sql);
ps.setInt(1, user.getId());
ps.setString(2, user.getUsername());
ps.setString(3, user.getPassword());
n = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.conClose(con, ps, null);
}
if (n > 0) {
return true;
}
return false;
}
// 查询
public static User query(String name) {
Connection con = JdbcUtil.getConnection();
String sql = "select * from Tuser where username=?";
PreparedStatement ps = null;
ResultSet rs = null;// 定义一个结果集
User user = null;
try {
ps = con.prepareStatement(sql);
ps.setString(1, name);
rs = ps.executeQuery();
while (rs.next()) {
user = new User();
user.setId(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.conClose(con, ps, rs);
}
return user;
}
二、c3p0连接数据库
配置文件dataSource.properties:
c3p0.driverClass=oracle.jdbc.OracleDriver
c3p0.jdbcUrl=jdbc:oracle:thin:@127.0.0.0:15211:orcl
c3p0.user=root
c3p0.password=root
c3p0.minPoolSize=0
c3p0.maxPoolSize=10
#连接的最大空闲时间,如果超过这个时间,连接还没被使用,则会断开
c3p0.maxIdleTime=60
#连接池无空闲连接时一次性创建新连接的个数
c3p0.acquireIncrement=2
#连接池为数据源缓存的PreparedStatement总数。为0不缓存
c3p0.maxStatements=0
#连接池获得新连接失败时重试额次数
c3p0.acquireRetryAttempts=30
DataBaseName=Oracle
当然也可以配置在xml里,这里就配在properties文件里了。
工具类:
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import org.springframework.jdbc.core.JdbcTemplate;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class c3p0Util {
protected static ComboPooledDataSource ds = new ComboPooledDataSource(true);
protected static JdbcTemplate jdbcTemplate;
static {
try {
initDBInFo();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @throws IOException
* @throws PropertyVetoException
* @Title: initDBInFo
* @Description: 初始化数据库连接池
*/
private static void initDBInFo() throws IOException, PropertyVetoException {
Properties pro = new Properties();
InputStream in = Thread.currentThread().getContextClassLoader()
.getResourceAsStream("dataSource.properties");
pro.load(in);
try {
ds.setDriverClass(pro.getProperty("c3p0.driverClass"));
ds.setJdbcUrl(pro.getProperty("c3p0.jdbcUrl"));
ds.setUser(pro.getProperty("c3p0.user"));
ds.setPassword(pro.getProperty("c3p0.password"));
ds.setMinPoolSize(Integer.valueOf(pro.getProperty("c3p0.minPoolSize")));
ds.setMaxPoolSize(Integer.valueOf(pro.getProperty("c3p0.maxPoolSize")));
ds.setMaxIdleTime(Integer.valueOf(pro.getProperty("c3p0.maxIdleTime")));
ds.setAcquireIncrement(Integer.valueOf(pro.getProperty("c3p0.acquireIncrement")));
ds.setMaxStatements(Integer.valueOf(pro.getProperty("c3p0.maxStatements")));
ds.setIdleConnectionTestPeriod(Integer.valueOf(pro.getProperty("c3p0.idleConnectionTestPeriod")));
ds.setAcquireRetryAttempts(Integer.valueOf(pro.getProperty("c3p0.acquireRetryAttempts")));
jdbcTemplate = new JdbcTemplate(ds);
} catch (NumberFormatException e) {
System.out.println("数据库连接池初始化失败:" + e);
e.printStackTrace();
}
}
}
这里用到了spring的JdbcTemplate,需要自己下载jar包哦。
测试类:
String sql = "select * from Tuser";
try {
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
System.out.println(list);
} catch (Exception e) {
e.printStackTrace();
}
大功告成!