通过读取propertiesGet链接Database信息，利用apache开源Database操作Tools包DBUtils进行DatabaseCRUD操作。
propertiesFile：
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=11111通过dbutils自定义GetDatabase连接对象:
package learn.javase.jdbc;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class JdbcUtils{
private static Connection conn;
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
try {
getReadConfig();
Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
}catch(Exception e) {
new RuntimeException("链接数据库失败！");
}
}
private static void getReadConfig() throws Exception{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
pro.load(in);
driverClass =pro.getProperty("driverClass");
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
}
public static Connection getConnection() {
return conn;
}
}
UsageTools包中的QueryRunner进行Database的CRUD操作：
package learn.javase.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
/**
* 利用apatche的DBUtils工具包，添加\修改\删除数据
* @author Jole
* */
public class DbUtilsDemo {
private static Connection conn = JdbcUtils.getConnection();
private static QueryRunner qr = new QueryRunner();
public static void main(String[] args) throws Exception{
// insert();
// update();
delte();
}
public static void insert() throws SQLException{
String sql = "insert into my_user(u_id,u_name,u_age) values(11111,'inster',10)";
Object[] param = {};
int n = qr.update(conn, sql, param);
System.out.println(n);
DbUtils.close(conn);
}
public static void update() throws SQLException{
String sql ="update my_user set u_name=? ,u_age=? where u_id=?";
Object[] param = {"ceshi",1000,11111};
int n = qr.update(conn, sql, param);
DbUtils.close(conn);
System.out.println(n);
}
public static void delte() throws SQLException{
String sql = "delete from my_user where u_id=?";
int n = qr.update(conn, sql, 11111);
DbUtils.close(conn);
System.out.println(n);
}
}
封装QueryRunnerReturn结果成对象： 要封装成的对象User
package learn.javase.jdbc;
public class User {
private String u_id;
private String u_name;
private String u_age;
public String getU_id() {
return u_id;
}
public void setU_id(String u_id) {
this.u_id = u_id;
}
public String getU_name() {
return u_name;
}
public void setU_name(String u_name) {
this.u_name = u_name;
}
public String getU_age() {
return u_age;
}
public void setU_age(String u_age) {
this.u_age = u_age;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(String u_id, String u_name, String u_age) {
super();
this.u_id = u_id;
this.u_name = u_name;
this.u_age = u_age;
}
@Override
public String toString() {
return "User [u_id=" + u_id + ", u_name=" + u_name + ", u_age=" + u_age + "]";
}
}
根据Return结果，通过ImplementationResultHandlerInterface的Implementation类，封装对象:
package learn.javase.jdbc;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
/**
* 利用开源apache的commons-dbutils-1.7.jar的QueryRunner封装数据
* @author Jole
* */
public class QueryRunnerDemo01 {
public static final Connection conn = JdbcUtils.getConnection();
public static final QueryRunner qr = new QueryRunner();
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
// getInfo();
// getInfo2();
// getInfo3();
// getInfo4();
// getInfo5();
// getInfo6();
// getInfo7();
getInfo8();
}
//ArrayHandler 第一行
public static void getInfo() throws Exception{
String sql ="select * from my_user";
Object[] rs = qr.query(conn, sql, new ArrayHandler());
for(Object obj : rs) {
System.out.print(obj + "\t");
}
}
//ArrayListHandler 所有记录
public static void getInfo2() throws Exception{
String sql ="select * from my_user";
List<Object[]> rs = qr.query(conn, sql, new ArrayListHandler());
for(Object[] objs : rs) {
for(Object obj : objs) {
System.out.print(obj + "\t");
}
System.out.println();
}
}
//javaBean 第一行封装成对象
public static void getInfo3() throws Exception{
String sql ="select * from my_user";
User user = qr.query(conn, sql, new BeanHandler<User>(User.class));
System.out.println(user);
}
//javaBean 所有记录封装成对象
public static void getInfo4() throws Exception{
String sql ="select * from my_user";
List<User> userList = qr.query(conn, sql, new BeanListHandler<User>(User.class));
for(User user : userList) {
System.out.println(user);
}
}
//ColumnListHandler所有记录的某列值
public static void getInfo5() throws Exception{
String sql ="select * from my_user";
List<Object> list = (List<Object>) qr.query(conn, sql, new ColumnListHandler("u_name"));
for(Object obj : list) {
System.out.println(obj);
}
}
//ScalarHandler返回单个值
public static void getInfo6() throws Exception{
String sql ="select count(*) from my_user";
Long count = qr.query(conn, sql, new ScalarHandler<Long>());
System.out.println(count);
}
//MapHandler将第一行数据封到map中
public static void getInfo7() throws Exception{
String sql ="select * from my_user";
Map<String, Object> map = qr.query(conn, sql, new MapHandler());
Set<String> set =map.keySet();
for(String key : set) {
System.out.println(key+" "+map.get(key));
}
}
//MapListHandler将所有数据封到list中的map中
public static void getInfo8() throws Exception{
String sql ="select * from my_user";
List<Map<String, Object>> list= qr.query(conn, sql, new MapListHandler());
for(Map<String, Object> map : list) {
for(String key : map.keySet()) {
System.out.print(key+" "+ map.get(key));
}
System.out.println();
}
}
}
主要涉及的jar包：commons-dbutils-1.7.jar（基本的CRUD）、mysql-connector-java-5.1.37-bin.jar（Database连接驱动）