JDBC如何連接不同類型數(shù)據(jù)庫
一、連接數(shù)據(jù)庫
1. 連接mysql數(shù)據(jù)庫
Connection conn = null; //數(shù)據(jù)庫連接驅動名:針對不同的數(shù)據(jù)庫,驅動名稱不同,但是同一種類型的數(shù)據(jù)庫改字符串相同 Class.forName("com.mysql.jdbc.Driver"); String url="JDBC:mysql://localhost:8080/testDB"; String user="test"; String password="test"; try { //1.加載驅動 Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫成功!"); PreparedStatement statement = null; //舉個查詢例子測試一下 //查詢userinfo表的數(shù)據(jù) statement = conn .prepareStatement("select * from userInfo"); ResultSet res = null; res = statement.executeQuery(); //當查詢下一行有記錄時:res.next()返回值為true,反之為false while (res.next()) { String TenantCode = res.getString("TenantCode"); String TenantName = res.getString("TenantName"); String Cloud = res.getString("Cloud"); System.out.println("學號:" + TenantCode + "姓名:" + TenantName + " 性別:" + Cloud); } } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫失?。?); }
2.連接sqlserver數(shù)據(jù)庫
Connection conn = null; Class.forName("com.microsoft.JDBC.sqlserver.SQLServerDriver"); String url="JDBC:microsoft:sqlserver://localhost:1433;DatabaseName=testDb"; String user="test"; String password="test"; try { //1.加載驅動 Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫成功!"); } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫失敗!"); }
3. 連接postgresql數(shù)據(jù)庫
Connection conn = null; //數(shù)據(jù)庫連接驅動名:針對不同的數(shù)據(jù)庫,驅動名稱不同,但是同一種類型的數(shù)據(jù)庫改字符串相同 Class.forName("org.postgresql.Driver"); String url="JDBC:postgresql://localhost/testDb"; String user="test"; String password="test"; try { //1.加載驅動 Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫成功!"); } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫失敗!"); }
4. 連接Oracle 數(shù)據(jù)庫
Connection conn = null; Class.forName("oracle.JDBC.driver.OracleDriver"); String url="JDBC:oracle:thin:@localhost:1521:orcl"http://orcl為Oracle數(shù)據(jù)庫的SID String user="test"; String password="test"; try { //1.加載驅動 Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫成功!"); } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫失??!"); }
二、JDBCUtils(數(shù)據(jù)庫常用操作工具類)
(1)獲取數(shù)據(jù)庫連接對象
(2)獲取查詢結果集
(3)將查詢結果集轉化為指定對象(使用者自行提供類參數(shù),通過反射和轉化)
(4)執(zhí)行更新、刪除、插入操作
(5)關閉數(shù)據(jù)庫連接
package utils; import java.lang.reflect.Constructor; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 數(shù)據(jù)庫常用操作工具類 * */ public final class JdbcUtils { // mysql8驅動 public static final String MYSQL8_DRIVER = "com.mysql.cj.jdbc.Driver"; /** * 結果集處理器 * */ public static interface ResultSetHandler<T> { /** * 將一個結果集轉換成T類型 * * @param rs * @return */ T handle(ResultSet rs); } /** * 列處理器 * */ public static interface ColumnHandler<T> { /** * * @param method 根據(jù)列名自動匹配的方法名 * @param columnName 列名 * @param t 對象 * @param value 值 * @return 返回true,表示用戶已處理完成,無需再處理,返回false,則代表用戶不處理 */ boolean handleColumn(Method method, String columnName, T t, Object value); } /** * 內部類的目的,就是為了將結果集中的數(shù)據(jù)自動封裝成對象 * */ public static class BeanListHandler<T> implements ResultSetHandler<List<T>> { private final Class<T> clazz; private ColumnHandler<T> columnHandler; public BeanListHandler(Class<T> clazz) { this.clazz = clazz; } public BeanListHandler(Class<T> clazz, ColumnHandler<T> columnHandler) { this.clazz = clazz; this.columnHandler = columnHandler; } @Override public List<T> handle(ResultSet rs) { // 返回值 List<T> list = new ArrayList<>(); // 存儲所有列名(別名) List<String> columnNames = new ArrayList<>(); // 存儲所有方法,鍵名是列名(別名),值即其對應的setter方法 Map<String, Method> methodMap = new HashMap<>(); // 獲取所有列名 try { // 結果集元數(shù)據(jù) ResultSetMetaData rsmd = rs.getMetaData(); // 返回查詢結果集的列數(shù) int count = rsmd.getColumnCount(); // 返回此類型的所有方法 Method[] methods = clazz.getDeclaredMethods(); for (int i = 0; i < count; i++) { // 獲取列名,如果起別名,則獲取別名 String columnName = rsmd.getColumnLabel(i + 1); columnNames.add(columnName);// 返回查詢結果集的列名 // 組裝出對象的方法名 String methodName = columnName.substring(0, 1).toUpperCase() + columnName.substring(1); methodName = "set" + methodName; for (Method me : methods) { if (me.getName().equals(methodName)) { methodMap.put(columnName, me);// 設置到map中 break; } } } // 準備工作已完成,將結果集中的數(shù)據(jù)轉換成T類型的實例 if (rs != null) { // 獲取無參的構造方法 Constructor<T> con = clazz.getDeclaredConstructor(); while (rs.next()) { T t = con.newInstance();// T類型的實例 for (int i = 0; i < count; i++) { String columnName = columnNames.get(i); // 從結果集中取出對應列的數(shù)據(jù) Object value = rs.getObject(columnName); // 取出方法 Method method = methodMap.get(columnName); if (method != null) { if (columnHandler != null) { boolean done = columnHandler.handleColumn(method, columnName, t, value); if (!done) { // 通過反射給T類型的實例賦值 method.invoke(t, value); } } } } list.add(t); } } return list; } catch (Exception e) { e.printStackTrace(); } return null; } } /** * 獲取數(shù)據(jù)庫連接 * * @param url * @param user * @param password * @return */ public static final Connection getConnection(String driver, String url, String user, String password) { try { Class.forName(driver); return DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } throw new RuntimeException("無法創(chuàng)建數(shù)據(jù)庫連接"); } /** * 獲取數(shù)據(jù)庫連接 * * @param url * @param user * @param password * @return */ public static final Connection getConnection(String url, String user, String password) { return getConnection(MYSQL8_DRIVER, url, user, password); } /** * 執(zhí)行查詢操作,返回結果集 * * @param conn * @param sql * @param args * @return */ private static final ResultSet query(Connection conn, String sql, Object[] args) { try { PreparedStatement ps = conn.prepareStatement(sql); if (args != null) { // 給PreparedStatement實例設置參數(shù) for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } } return ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } throw new RuntimeException("查詢出現(xiàn)異常"); } /** * 返回對象的集合 * * @param <T> * @param conn * @param sql * @param args * @return */ public static final <T> T query(Connection conn, ResultSetHandler<T> handler, String sql, Object[] args) { ResultSet rs = query(conn, sql, args); return handler.handle(rs); } /** * 寫操作 * * @return 返回受影響的行數(shù) */ public static final int update(Connection conn, String sql, Object[] args) { try { PreparedStatement ps = conn.prepareStatement(sql); if (args != null) { // 給PreparedStatement實例設置參數(shù) for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } } return ps.executeUpdate(); } catch (SQLException e) { // e.printStackTrace(); } return -1; } /** * 關閉數(shù)據(jù)庫連接 * * @param conn */ public static final void closeConnection(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
三、自定義JDBCUtils
我采用的是:
- JDBCUtils來連接和關閉數(shù)據(jù)庫相關資源。
- 再寫一個類(BeanJDBCUtils)來進行數(shù)據(jù)庫的基本操作。
- 最后寫一個類來實現(xiàn)具體的操作。
1. JDBCUtils
/** * @author long * @date 2022/6/15 14:00 * @Description: 連接數(shù)據(jù)庫的類 */ @SuppressWarnings("all") public class JDBCUtils { private static final String url; private static final String userName; private static final String password; private static final String driver; static { Properties properties = new Properties(); InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"); try { properties.load(is); url = properties.getProperty("url"); userName = properties.getProperty("userName"); password = properties.getProperty("password"); driver = properties.getProperty("driver"); //注冊驅動 Class.forName(driver); } catch (Exception e) { throw new RuntimeException(e); } } /** * @Description 獲取數(shù)據(jù)庫的連接 * @return */ public Connection getConnection() { try { return DriverManager.getConnection(url, userName, password); } catch (SQLException e) { throw new RuntimeException("無法連接到數(shù)據(jù)庫\n"); } } /** * @Description 關閉連接 * @param connection */ public void close(Connection connection) { try { connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } } /** * @Description 關閉連接 * @param connection */ public void close(Connection connection, ResultSet resultSet) { try { connection.close(); resultSet.close(); } catch (SQLException e) { throw new RuntimeException(e); } } }
2. BeanJDBCUtils
/** * @author long * @date 2022/6/15 15:23 * @Description: 對數(shù)據(jù)的操作的基本類 */ @SuppressWarnings("all") public class BeanJDBCUtils<T> extends JDBCUtils { private Connection connection; private PreparedStatement preparedStatement; private Class entityClass; /** * 構造方法,為了獲取到泛型的對象的類,將其賦值給entityClass */ public BeanJDBCUtils() { //getClass() 獲取Class對象,當前我們執(zhí)行的是new FruitDAOImpl() , 創(chuàng)建的是FruitDAOImpl的實例 //那么子類構造方法內部首先會調用父類(BaseDAO)的無參構造方法 //因此此處的getClass()會被執(zhí)行,但是getClass獲取的是FruitDAOImpl的Class //所以getGenericSuperclass()獲取到的是BaseJDBCUtils的Class //System.out.println("獲取父類對象:" + clazz.getSuperclass()); Type genericType = getClass().getGenericSuperclass(); //ParameterizedType 參數(shù)化類型 Type[] actualTypeArguments = ((ParameterizedType) genericType).getActualTypeArguments(); //獲取到的<T>中的T的真實的類型 Type actualType = actualTypeArguments[0]; try { entityClass = Class.forName(actualType.getTypeName()); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * @param sql * @param args * @return * @Description: 查找數(shù)據(jù)庫中多行數(shù)據(jù) */ public List<T> query(String sql, Object... args) { List<T> list = new ArrayList<>(); connection = getConnection(); ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); /*填充占位符*/ for (int i = 0; i < args.length; ++i) preparedStatement.setObject(i + 1, args[i]); preparedStatement.execute(); /*獲取返回結果*/ resultSet = preparedStatement.getResultSet(); /*獲取返回結果的數(shù)據(jù)*/ ResultSetMetaData metaData = resultSet.getMetaData(); /*獲取數(shù)據(jù)的行數(shù)*/ int columnCount = metaData.getColumnCount(); while (resultSet.next()) { /*初始化泛型*/ T entity = (T) entityClass.newInstance(); Object object = new Object(); for (int i = 0; i < columnCount; ++i) { /*獲取列名的別名,如果沒有別名則直接獲取列名*/ String columnLabel = metaData.getColumnLabel(i + 1); object = resultSet.getObject(i + 1); /*使用反射給泛型變量賦值*/ Field field = entity.getClass().getDeclaredField(columnLabel); field.setAccessible(true); field.set(entity, object); } list.add(entity); } return list; } catch (Exception e) { throw new RuntimeException(e); } finally { close(connection, resultSet); } } /** * * @param sql * @param clazz * @param args * @return * @Description: 查找數(shù)據(jù)庫中單行數(shù)據(jù) */ public T query(String sql, Class clazz, Object... args) { connection = getConnection(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < args.length; ++i) preparedStatement.setObject(i + 1, args[i]); resultSet = preparedStatement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); T t = (T) entityClass.newInstance(); if (resultSet.next()) { Object object = new Object(); for (int i = 0; i < columnCount; i++) { String columnLabel = metaData.getColumnLabel(i + 1); Field field = t.getClass().getDeclaredField(columnLabel); field.setAccessible(true); object = resultSet.getObject(columnLabel); field.set(t, object); } } return t; } catch (Exception e) { throw new RuntimeException(e); } finally { close(connection, resultSet); } } /** * @param sql * @param args * @return 影響的行數(shù) * @Description: 增刪改 */ public Integer updata(String sql, Object... args) { connection = getConnection(); PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); for (int i = 0; i < args.length; ++i) preparedStatement.setObject(i + 1, args[i]); return preparedStatement.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } finally { close(connection); } } /** * @param sql * @param args * @param <E> * @return * @Description: 查找特定的值,并返回單一基本類型 */ public <E> E getElement(String sql, Object... args) { connection = getConnection(); ResultSet resultSet = null; try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) { for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i + 1, args[i]); } Object object = null; resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { object = resultSet.getObject(1); } return (E) object; } catch (SQLException e) { throw new RuntimeException(e); } finally { close(connection, resultSet); } } }
創(chuàng)建一個與數(shù)據(jù)庫表t_fruit一一對應的Fruit類
import lombok.*; /** * @author long * @date 2022/6/15 14:58 * @Description: 與數(shù)據(jù)庫中的t_fruit表一一對應的水果類 */ @ToString @Data @NoArgsConstructor @AllArgsConstructor @SuppressWarnings("all") public class Fruit { private Integer fid; private String fname; private Integer price; private Integer fcount; private String remark; public Fruit(String fname, Integer price, Integer fcount, String remark) { this.fname = fname; this.price = price; this.fcount = fcount; this.remark = remark; } }
創(chuàng)建一個接口(FruitDAO)來聲明對改數(shù)據(jù)庫的表中的操作的方法。
import java.util.List; public interface FruitDAO { /*獲取所有水果信息*/ List<Fruit> getAllFruit(); /*添加水果信息*/ Integer addFruit(Fruit fruit); /*獲取水果的種類的數(shù)量*/ Long getAllCount(); /*獲取最大價格*/ Integer getMaxPrice(); /*獲取最小價格*/ Integer getMinPrice(); Fruit getFruitById(Integer id); }
對上述接口(FruitDAO)的實現(xiàn)類(FruitDAOImpl)
/** * @author long * @date 2022/6/15 15:04 * @Description: 對數(shù)據(jù)庫中t_fruit表的操作的類 */ public class FruitDAOImpl extends BeanJDBCUtils<Fruit> implements FruitDAO { @Override public List<Fruit> getAllFruit() { String sql = "select * from t_fruit"; return query(sql); } @Override public Integer addFruit(Fruit fruit) { String sql = "insert into t_fruit(fname,price,fcount,remark) values(?,?,?,?)"; return updata(sql, fruit.getFname(), fruit.getPrice(), fruit.getFcount(), fruit.getRemark()); } @Override public Long getAllCount() { String sql = "select count(*) from t_fruit"; return getElement(sql); } @Override public Integer getMaxPrice() { String sql = "select max(price) from t_fruit"; return getElement(sql); } @Override public Integer getMinPrice() { String sql = "select min(price) from t_fruit"; return getElement(sql); } @Override public Fruit getFruitById(Integer id) { String sql = "select * from t_fruit where fid = ?"; return query(sql, Fruit.class, id); } }
總結
以上為個人經驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
mysql百萬數(shù)據(jù)表加索引優(yōu)化的方法
在大數(shù)據(jù)時代,隨著數(shù)據(jù)量的快速增長,對數(shù)據(jù)庫的索引優(yōu)化變得尤為重要,本文主要介紹了mysql百萬數(shù)據(jù)表加索引優(yōu)化的方法,感興趣的可以了解一下2024-02-02MySQL動態(tài)字符串處理DYNAMIC_STRING
本文主要給大家簡單講解了mysql如何使用DYNAMIC_STRING來進行動態(tài)字符串的保存,非常的實用,有需要的小伙伴可以參考下2016-10-10MySQL數(shù)據(jù)庫優(yōu)化推薦的編譯安裝參數(shù)小結
這篇文章主要介紹了MySQL數(shù)據(jù)庫優(yōu)化推薦的編譯安裝參數(shù)小結,需要的朋友可以參考下2015-04-04