Java-jdbc工具類DBUtils

標籤：time   object   main   http   man   test   close   source   puts   

建立項目：

 

 

匯入相應jar包：

　　看。

 

JDBCUtil.java擷取資料庫連接檔案：

package com.gordon.jdbcutil;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;public class JDBCUtil {public static String DRIVERNAME = null;public static String URL = null;public static String USER = null;public static String PASSWORD = null;public static Connection conn = null;static {try {Properties props = new Properties();//Reader in = new FileReader("db.properties");InputStream in = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");props.load(in);DRIVERNAME = props.getProperty("drivername");URL = props.getProperty("url");USER = props.getProperty("user");PASSWORD = props.getProperty("password");} catch (Exception e) {throw new RuntimeException(e);}}public static Connection getConnection() throws Exception {if (conn != null) {return conn;}Class.forName(DRIVERNAME);conn = DriverManager.getConnection(URL, USER, PASSWORD);return conn;}public static void closeResource(Connection conn, PreparedStatement st) throws SQLException {st.close();conn.close();}public static void closeResource(Connection conn, ResultSet rs, PreparedStatement st) throws SQLException {st.close();rs.close();conn.close();}}

　　

JavaBean類編寫（User.java）:

package com.gordon.bean;public class User {private int id;private String name;public User() {}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}

　　

JDBCUtils.java資料庫工具類：

package com.gordon.jdbcutils;import java.sql.Connection;import java.util.List;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.ScalarHandler;import com.gordon.bean.User;import com.gordon.jdbcutil.JDBCUtil;public class JDBCUtils {public static void main(String[] args) {try {// insertFunction();// updateFunction();// deleteFunction();selectFunction();} catch (Exception e) {System.out.println(e.getMessage());}}public static void selectFunction() throws Exception {/*ArrayHandler將結果集中的第一條記錄封裝到一個Object[]數組中，數組中的每一個元素就是這條記錄中的每一個欄位的值ArrayListHandler將結果集中的每一條記錄都封裝到一個Object[]數組中，將這些數組在封裝到List集合中。BeanHandler將結果集中第一條記錄封裝到一個指定的javaBean中。BeanListHandler將結果集中每一條記錄封裝到指定的javaBean中，將這些javaBean在封裝到List集合中ColumnListHandler將結果集中指定的列的欄位值，封裝到一個List集合中ScalarHandler它是用於單資料。例如select count(*) from 表操作。 */QueryRunner qr = new QueryRunner();Connection conn = JDBCUtil.getConnection();/** ArrayHandler */// 1String sql = "SELECT * FROM user LIMIT ?";Object[] params = { 1 };Object[] objArrayhandler = qr.query(conn, sql, new ArrayHandler(), params);System.out.println(objArrayhandler[0] + ":" + objArrayhandler[1]);// 2String sql = "SELECT * FROM user";Object[] params = {};List<Object[]> listobjArrayhandler = qr.query(conn, sql, new ArrayListHandler(), params);for (Object[] obj : listobjArrayhandler) {System.out.println(obj[0] + ":" + obj[1]);}/** BeanHandler */// 1String sql = "SELECT * FROM user LIMIT 1";Object[] params = {};User user = qr.query(conn, sql, new BeanHandler<User>(User.class), params);System.out.println(user.getId() + ":" + user.getName());// 2String sql = "SELECT * FROM user";Object[] params = {};List<User> userList = qr.query(conn, sql, new BeanListHandler<User>(User.class), params);for (User user : userList) {System.out.println(user.getId() + ":" + user.getName());}/** ColumnListHandler */// 1String sql = "SELECT max(id) FROM user";Object[] params = {};int maxid = qr.query(conn, sql, new ScalarHandler<Integer>(), params);System.out.println(maxid);// 2String sql = "SELECT name FROM user";Object[] params = {};List<String> userNameList = qr.query(conn, sql, new ColumnListHandler<String>(), params);for (String userName : userNameList) {System.out.println(userName);}}public static void deleteFunction() throws Exception {QueryRunner qr = new QueryRunner();String sql = "DELETE FROM user WHERE ID = ?";Object[] param = { 5 };Connection conn = JDBCUtil.getConnection();int res = qr.update(conn, sql, param);if (res > 0) {System.out.println("delete success.");} else if (res == 0) {System.out.println("deleted.");} else {System.out.println("delete faild.");}}public static void updateFunction() throws Exception {QueryRunner qr = new QueryRunner();String sql = "UPDATE user SET name = ? WHERE ID = ?";Object[] param = { "newname", 5 };Connection conn = JDBCUtil.getConnection();int res = qr.update(conn, sql, param);if (res > 0) {System.out.println("update success.");} else {System.out.println("update faild.");}}public static void insertFunction() throws Exception {/** 擷取執行對象 */QueryRunner qr = new QueryRunner();/** sql */String sql = "INSERT INTO user (name) VALUES (?)";/** 參數 */Object[] param = { "testname" };/** 擷取資料庫連接 */Connection conn = JDBCUtil.getConnection();/** 執行sql */int res = qr.update(conn, sql, param);/** 處理結果 */if (res > 0) {System.out.println("insert success.");} else {System.out.println("insert faild.");}}}

　　

 

Java-jdbc工具類DBUtils