使用第三方jar包连接数据库
1.选用commons-dbutils-1.6.jar包
* commons-dbutils-1.6.jar介绍
* 可以操作jdbc连接数据库并且执行SQL语句
* 处理返回的结果集
* 三个核心类:
* DbUtils 一些关闭资源的方法(安静的关闭 不用异常处理)
* QueryRunner 执行SQL语句 并且返回你想要的结果集
* 参数 ResultSetHandler 接口(使用实现类 想要什么结果集就传入什么该接口的实现类)
2.必要jar
mysql-connector-java-5.1.37-bin.jar
1.创建dbinfo.properties文本文件
写入匹配信息
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:
user=root
password=123456
2.创建JDBCUtil类后获取文本信息连接数据库
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
driverClass=rb.getString("driverClass");
url=rb.getString("url");
user=rb.getString("user");
password=rb.getString("password");
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection connection = null;
try {
connection = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("连接失败");
}
return connection;
}
public static void closeAll(ResultSet resultSet,
Statement statement,Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e2) {
throw new RuntimeException("关闭失败");
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e2) {
throw new RuntimeException("关闭失败");
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e2) {
throw new RuntimeException("关闭失败");
}
connection = null;
}
}
}
3.创建Sort类 作为与表对应的 javabean实体类
public class Sort {
private int sid;
private String sname;
private double sprice;
private String sdesc;
public Sort() {
super();
}
public Sort(int sid, String sname, double sprice, String sdesc) {
super();
this.sid = sid;
this.sname = sname;
this.sprice = sprice;
this.sdesc = sdesc;
}
@Override
public String toString() {
return "Sort [sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public double getSprice() {
return sprice;
}
public void setSprice(double sprice) {
this.sprice = sprice;
}
public String getSdesc() {
return sdesc;
}
public void setSdesc(String sdesc) {
this.sdesc = sdesc;
}
}
3.使用第三方commons-dbutils-1.6.jar包进行观察观察
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
public class Demo01 {
public static void main(String[] args) throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "update sort set sname=?,sprice=?,sdesc=? where sid=5";
Object[] params= {"咖啡",1000,"java里的咖啡"};
int row = qr.update(connection,sql,"咖啡",1000,"java里的咖啡");
if (row > 0) {
System.out.println("修改成功");
}
DbUtils.close(connection);
}
private static void insert() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = null;
connection = JDBCUtil.getConnection();
String sql = "insert into sort value(null,'显示器',5,'这是一台显示器')";
int row = qr.update(connection, sql);
if (row > 0) {
System.out.println("插入成功");
}
DbUtils.close(connection);
}
}
4.查询操作
1. ArrayHandler:把结果集中的第一行数据转成对象数组。
2. ArrayListHandler:把结果集中的每一行数据都转成一个对象数组,再存放到List中。
3. BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
4.BeanListHandler:将结果集中的每一行数据都封装到一个对应的JavaBean实例中,存放到List里。
5. ColumnListHandler:将结果集中某一列的数据存放到List中。
6. KeyedHandler:将结果集中的每一行数据都封装到一个Map里,然后再根据指定的key把每个Map再存放到一个Map里。
7. MapHandler:将结果集中的第一行数据封装到一个Map里,key是列名,value就是对应的值。
8. MapListHandler:将结果集中的每一行数据都封装到一个Map里,然后再存放到List。
9. ScalarHandler:将结果集中某一条记录的其中某一列的数据存成Object。
public class Demo02 {
private static final char[] Object = null;
private static final char[] Objec = null;
private static final java.lang.Object Value = null;
@Test
public void select1() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Object[] objects = qr.query(connection, sql,new ArrayHandler());
for(Object object : objects) {
System.out.println(object);
}
DbUtils.close(connection);
}
@Test
public void select2() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object[]> list = qr.query(conn, sql, new ArrayListHandler());
for (Object[] objects : list) {
for (Object object : objects) {
System.out.println(object);
}
System.out.println();
}
DbUtils.close(conn);
}
@Test
public void select3() throws SQLException{
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Sort sort = qr.query(connection, sql, new BeanHandler<>(Sort.class));
if (sort == null) {
System.out.println("没有该条数据");
}
System.out.println(sort);
}
@Test
public void select4() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Sort> sort = qr.query(conn, sql, new BeanListHandler<>(Sort.class));
if (sort == null) {
System.out.println("没有该条数据");
}
for (Sort sort2 : sort) {
System.out.println(sort2);
}
}
@Test
public void select5() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtil.getConnection();
String sql = "select * from sort";
List<String> list = qr.query(conn, sql, new ColumnListHandler<String>("sname"));
for(String string:list) {
System.out.println(string);
}
DbUtils.close(conn);
}
@Test
public void select6() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtil.getConnection();
String sql = "select avg(sid) from sort";
BigDecimal query = qr.query(conn,sql,new ScalarHandler<BigDecimal>());
System.out.println(query);
DbUtils.close(conn);
}
@Test
public void select7() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtil.getConnection();
String sql = "select * from sort";
Map<String,Object> query = qr.query(conn,sql,new MapHandler());
query.keySet();
query.values();
System.out.println(query.keySet());
System.out.println(query.values());
System.out.println(query);
for (String key : query.keySet()) {
System.out.println(key + "\t" + query.get(key));
}
DbUtils.close(conn);
}
@Test
public void select8() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection conn = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Map<String,Object>> query = qr.query(conn,sql,new MapListHandler());
for (Map<String, Object> map : query) {
for (String key : map.keySet()) {
System.out.println(map.keySet());
System.out.println(map.values());
}
}
DbUtils.close(conn);
}
}