package JDBC1;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class
Joint
{
public static void main(String[] args) throws SQLException {
//c3p0();
druid();
}
public static void
c3p0()
throws SQLException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
Connection connection = dataSource.getConnection();
System.out.println(connection);
connection.close();//归还连接
}
public static void
druid()
{
PreparedStatement preparedStatement=null;
Connection connection =null;
try {
connection = Tools.getConnection();
String sql = “insert into demo values(?,?)”;
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,8);
preparedStatement.setInt(2,300);
int count = preparedStatement.executeUpdate();
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
Tools.close(preparedStatement,connection);
}
}//druid
}//end
package JDBC1;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
import java.util.Map;
public class
JDBCTemplate
{
JdbcTemplate template=new JdbcTemplate(Tools.getDataSource());
//修改记录
@Test
public void
test0()
{
String sql = “update demo set num =111 where id=1”;
int update = template.update(sql);
}
@Test
//添加记录
public void test1(){
String sql = “insert into demo (id,num) values(?,?)”;
template.update(sql, 1, 66);
}
@Test
//删除记录
public void test2(){
String sql = “delete from demo where id=?”;
template.update(sql,1);
}
@Test
//查询一条记录并封装map
public void test3(){
String s = “select * from demo where id=?”;
Map<String, Object> map = template.queryForMap(s, 1);
System.out.println(map);
}
@Test
//查询所有记录封装为list
public void test4(){
String s = “select * from demo”;
List<Map<String, Object>> mapList = template.queryForList(s);
for (Map<String, Object> stringObjectMap : mapList) {
System.out.println(stringObjectMap);
}
}
@Test
//查询所有记录封装为emp的list集合
public void test5(){
String s = “select * from demo”;
List list = template.query(s, new BeanPropertyRowMapper<>(Emp.class));
for (Emp emp : list) {
System.out.println(emp);
}
}
@Test
//查询总记录
public void test6(){
String s = “select count(id) from demo”;
Long aLong = template.queryForObject(s, Long.class);
System.out.println(aLong);
}//test5
}//end
package JDBC1;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class
Tools
{
private static DataSource dataSource;
static {
try {
Properties properties = new Properties();
properties.load(Tools.class.getClassLoader().getResourceAsStream(“druid.properties”));
dataSource= DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}//static
/**
- 获取连接
*/
public static Connection
getConnection()
throws SQLException {
return dataSource.getConnection();
}
/**
- 释放资源
*/
public static void
close
(ResultSet resultSet, Statement statement, Connection connection){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}//close
public static void close(Statement statement, Connection connection){
close(null,statement,connection);
}
/**
- 获取连接池方法
*/
public static DataSource
getDataSource()
{
return dataSource;
}
}//end
package JDBC1;
public class
Emp
{
private int id;
private int num;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
@Override
public String toString() {
return “Emp{” +
“id=” + id +
“, num=” + num +
‘}’;
}
}
c3p0-config.xml
<c3p0-config>
<!-- c3p0默认配置,下面还可以配置多个数据库 -->
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/db1?serverTimezone=GMT</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 初始化时获取连接数 -->
<property name="initialPoolSize">6</property>
<!-- 连接池中保留的最大连接数 -->
<property name="maxPoolSize">50</property>
<!-- 最大空闲时间,多少秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0 -->
<property name="maxIdleTime">1000</property>
</default-config>
</c3p0-config>
druid.properties
diver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1?serverTimezone=GMT
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000