使用这两个开源库都需要导包,maven管理jar包在Day7_2中介绍,deutils需要一个包,jdbctemplate全部需要5个包;
先介绍JdbcTemplate的使用:
首先导入5个jar包
有需要自行到主页的gitee仓库取
先创建工具类获取数据库连接对象–>通过getconnection()方法
package Day7.jdbctemplate;
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;
/**
* 以下链接Druid的工具类
* 这里暂时还是使用德鲁伊的数据库连接池,记得写配置文件jdbc,properties
*/
public class JDBCUtil {
//创建Datasource对象
private static DataSource ds;
static {
//创建Properties对象
Properties pro = new Properties();
try {
pro.load(JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 返回链接对象
*/
public static Connection getconnection() throws SQLException {
return ds.getConnection();
}
/**
* 关闭流
*/
public static void close(Statement sta, Connection con) {
if (sta != null) {
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();//归还连接
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭三个流
*/
public static void close(ResultSet rs, Statement sta, Connection con) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (sta != null) {
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();//归还连接
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取数据源
*/
public static DataSource getDatasource() {
return ds;
}
}
下面是测试类,介绍jdbctemplate的初级用法
package Day7.jdbctemplate;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* 以下程序是使用Jdbctemplate初级方法
*/
public class test {
public static void main(String[] args) {
//1、导入5个Jar包
//2、新建对象
JdbcTemplate jdbc = new JdbcTemplate(JDBCUtil.getDatasource());
//3、编写sql语句
String sql = "update money set typein='kk' where id=? ";
//4、执行修改语句
int count = jdbc.update(sql, 17);
//5、打印输出
System.out.println(count);
}
}
下面介绍DbUntils的应用:
需要导入的jar包如下
主页仓库自取
先给一个德鲁伊的工具类来获取连接
package Day7.dbUntil;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DbUtil {
private static DruidDataSource ds;
// 将线程和连接绑定,保证事务能统一执行
//数据库连接池,是将connection放进threadlocal里的,以保证每个线程从连接池中获得的都是线程自己的connection。
private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
//加载配置文件
static {
Properties properties = new Properties();
InputStream is = DbUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
properties.load(is);
ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取链接
public static Connection getConnection() {
Connection connection = THREAD_LOCAL.get();
try {
if (connection == null) {
connection = ds.getConnection();
THREAD_LOCAL.set(connection);
}
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//关闭资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
THREAD_LOCAL.remove();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
下面是一个数据操作层层的代码,使用dbuntils封装的数据操作类
这里没有调用上面的连接,采用的是传统的连接,可以更具需要更改
package Day7.dbUntil;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
public class BaseDAO {
/**
* 获取数据库连接
* @return
*/
public Connection getConnection() {
Connection conn = null;
String jdbcURL ="jdbc:mysql://localhost/changecard";
String jdbcDriver ="com.mysql.cj.jdbc.Driver";
String user = "root";
String password = "123456";
try {
DbUtils.loadDriver(jdbcDriver);
conn =DriverManager.getConnection(jdbcURL, user, password);
} catch (SQLException e) {
// handle the exception
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
return conn;
}
/**
* 查找多个对象
* @param sqlString
* @param clazz
* @return
*/
public List query(String sqlString,Class clazz) {
List beans = null;
Connection conn = null;
try {
conn = getConnection();
QueryRunner qRunner = new QueryRunner();
beans =
(List) qRunner.query(
conn,
sqlString,
new BeanListHandler(clazz));
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
return beans;
}
/**
* 查找对象
* @param sqlString
* @param clazz
* @return
*/
public Object get(String sqlString,Class clazz) {
List beans = null;
Object obj = null;
Connection conn = null;
try {
conn = getConnection();
QueryRunner qRunner = new QueryRunner();
beans =
(List) qRunner.query(
conn,
sqlString,
new BeanListHandler(clazz));
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
if(beans!=null &&!beans.isEmpty()){ //注意这里
obj=beans.get(0);
}
return obj;
}
/**
* 执行更新的sql语句,插入,修改,删除
* @param sqlString
* @return
*/
public boolean update(String sqlString) {
Connection conn = null;
boolean flag = false;
try {
conn = getConnection();
QueryRunner qRunner = new QueryRunner();
int i =qRunner.update(conn,sqlString);
if (i > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn);
}
return flag;
}
public static void main(String[] args) {
BaseDAO baseDAO = new BaseDAO();
System.out.println(baseDAO.update("select * from money"));
}
}