JDBC如何連接不同類型數(shù)據(jù)庫(kù)
一、連接數(shù)據(jù)庫(kù)
1. 連接mysql數(shù)據(jù)庫(kù)
Connection conn = null; //數(shù)據(jù)庫(kù)連接驅(qū)動(dòng)名:針對(duì)不同的數(shù)據(jù)庫(kù),驅(qū)動(dòng)名稱不同,但是同一種類型的數(shù)據(jù)庫(kù)改字符串相同 Class.forName("com.mysql.jdbc.Driver"); String url="JDBC:mysql://localhost:8080/testDB"; String user="test"; String password="test"; try { //1.加載驅(qū)動(dòng) Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫(kù)成功!"); PreparedStatement statement = null; //舉個(gè)查詢例子測(cè)試一下 //查詢userinfo表的數(shù)據(jù) statement = conn .prepareStatement("select * from userInfo"); ResultSet res = null; res = statement.executeQuery(); //當(dāng)查詢下一行有記錄時(shí):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("學(xué)號(hào):" + TenantCode + "姓名:" + TenantName + " 性別:" + Cloud); } } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫(kù)失?。?); }
2.連接sqlserver數(shù)據(jù)庫(kù)
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.加載驅(qū)動(dòng) Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫(kù)成功!"); } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫(kù)失??!"); }
3. 連接postgresql數(shù)據(jù)庫(kù)
Connection conn = null; //數(shù)據(jù)庫(kù)連接驅(qū)動(dòng)名:針對(duì)不同的數(shù)據(jù)庫(kù),驅(qū)動(dòng)名稱不同,但是同一種類型的數(shù)據(jù)庫(kù)改字符串相同 Class.forName("org.postgresql.Driver"); String url="JDBC:postgresql://localhost/testDb"; String user="test"; String password="test"; try { //1.加載驅(qū)動(dòng) Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫(kù)成功!"); } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫(kù)失敗!"); }
4. 連接Oracle 數(shù)據(jù)庫(kù)
Connection conn = null; Class.forName("oracle.JDBC.driver.OracleDriver"); String url="JDBC:oracle:thin:@localhost:1521:orcl"http://orcl為Oracle數(shù)據(jù)庫(kù)的SID String user="test"; String password="test"; try { //1.加載驅(qū)動(dòng) Class.forName(driver ); //2.連接 conn = DriverManager.getConnection(url, user, password); System.out.println("連接數(shù)據(jù)庫(kù)成功!"); } catch (Exception e) { e.printStackTrace(); System.out.println("連接數(shù)據(jù)庫(kù)失??!"); }
二、JDBCUtils(數(shù)據(jù)庫(kù)常用操作工具類)
(1)獲取數(shù)據(jù)庫(kù)連接對(duì)象
(2)獲取查詢結(jié)果集
(3)將查詢結(jié)果集轉(zhuǎn)化為指定對(duì)象(使用者自行提供類參數(shù),通過(guò)反射和轉(zhuǎn)化)
(4)執(zhí)行更新、刪除、插入操作
(5)關(guān)閉數(shù)據(jù)庫(kù)連接
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ù)庫(kù)常用操作工具類 * */ public final class JdbcUtils { // mysql8驅(qū)動(dòng) public static final String MYSQL8_DRIVER = "com.mysql.cj.jdbc.Driver"; /** * 結(jié)果集處理器 * */ public static interface ResultSetHandler<T> { /** * 將一個(gè)結(jié)果集轉(zhuǎn)換成T類型 * * @param rs * @return */ T handle(ResultSet rs); } /** * 列處理器 * */ public static interface ColumnHandler<T> { /** * * @param method 根據(jù)列名自動(dòng)匹配的方法名 * @param columnName 列名 * @param t 對(duì)象 * @param value 值 * @return 返回true,表示用戶已處理完成,無(wú)需再處理,返回false,則代表用戶不處理 */ boolean handleColumn(Method method, String columnName, T t, Object value); } /** * 內(nèi)部類的目的,就是為了將結(jié)果集中的數(shù)據(jù)自動(dòng)封裝成對(duì)象 * */ 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<>(); // 存儲(chǔ)所有列名(別名) List<String> columnNames = new ArrayList<>(); // 存儲(chǔ)所有方法,鍵名是列名(別名),值即其對(duì)應(yīng)的setter方法 Map<String, Method> methodMap = new HashMap<>(); // 獲取所有列名 try { // 結(jié)果集元數(shù)據(jù) ResultSetMetaData rsmd = rs.getMetaData(); // 返回查詢結(jié)果集的列數(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);// 返回查詢結(jié)果集的列名 // 組裝出對(duì)象的方法名 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);// 設(shè)置到map中 break; } } } // 準(zhǔn)備工作已完成,將結(jié)果集中的數(shù)據(jù)轉(zhuǎn)換成T類型的實(shí)例 if (rs != null) { // 獲取無(wú)參的構(gòu)造方法 Constructor<T> con = clazz.getDeclaredConstructor(); while (rs.next()) { T t = con.newInstance();// T類型的實(shí)例 for (int i = 0; i < count; i++) { String columnName = columnNames.get(i); // 從結(jié)果集中取出對(duì)應(yīng)列的數(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) { // 通過(guò)反射給T類型的實(shí)例賦值 method.invoke(t, value); } } } } list.add(t); } } return list; } catch (Exception e) { e.printStackTrace(); } return null; } } /** * 獲取數(shù)據(jù)庫(kù)連接 * * @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("無(wú)法創(chuàng)建數(shù)據(jù)庫(kù)連接"); } /** * 獲取數(shù)據(jù)庫(kù)連接 * * @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í)行查詢操作,返回結(jié)果集 * * @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í)例設(shè)置參數(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)異常"); } /** * 返回對(duì)象的集合 * * @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í)例設(shè)置參數(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; } /** * 關(guān)閉數(shù)據(jù)庫(kù)連接 * * @param conn */ public static final void closeConnection(Connection conn) { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
三、自定義JDBCUtils
我采用的是:
- JDBCUtils來(lái)連接和關(guān)閉數(shù)據(jù)庫(kù)相關(guān)資源。
- 再寫一個(gè)類(BeanJDBCUtils)來(lái)進(jìn)行數(shù)據(jù)庫(kù)的基本操作。
- 最后寫一個(gè)類來(lái)實(shí)現(xiàn)具體的操作。
1. JDBCUtils
/** * @author long * @date 2022/6/15 14:00 * @Description: 連接數(shù)據(jù)庫(kù)的類 */ @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"); //注冊(cè)驅(qū)動(dòng) Class.forName(driver); } catch (Exception e) { throw new RuntimeException(e); } } /** * @Description 獲取數(shù)據(jù)庫(kù)的連接 * @return */ public Connection getConnection() { try { return DriverManager.getConnection(url, userName, password); } catch (SQLException e) { throw new RuntimeException("無(wú)法連接到數(shù)據(jù)庫(kù)\n"); } } /** * @Description 關(guān)閉連接 * @param connection */ public void close(Connection connection) { try { connection.close(); } catch (SQLException e) { throw new RuntimeException(e); } } /** * @Description 關(guān)閉連接 * @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: 對(duì)數(shù)據(jù)的操作的基本類 */ @SuppressWarnings("all") public class BeanJDBCUtils<T> extends JDBCUtils { private Connection connection; private PreparedStatement preparedStatement; private Class entityClass; /** * 構(gòu)造方法,為了獲取到泛型的對(duì)象的類,將其賦值給entityClass */ public BeanJDBCUtils() { //getClass() 獲取Class對(duì)象,當(dāng)前我們執(zhí)行的是new FruitDAOImpl() , 創(chuàng)建的是FruitDAOImpl的實(shí)例 //那么子類構(gòu)造方法內(nèi)部首先會(huì)調(diào)用父類(BaseDAO)的無(wú)參構(gòu)造方法 //因此此處的getClass()會(huì)被執(zhí)行,但是getClass獲取的是FruitDAOImpl的Class //所以getGenericSuperclass()獲取到的是BaseJDBCUtils的Class //System.out.println("獲取父類對(duì)象:" + clazz.getSuperclass()); Type genericType = getClass().getGenericSuperclass(); //ParameterizedType 參數(shù)化類型 Type[] actualTypeArguments = ((ParameterizedType) genericType).getActualTypeArguments(); //獲取到的<T>中的T的真實(shí)的類型 Type actualType = actualTypeArguments[0]; try { entityClass = Class.forName(actualType.getTypeName()); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * @param sql * @param args * @return * @Description: 查找數(shù)據(jù)庫(kù)中多行數(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(); /*獲取返回結(jié)果*/ resultSet = preparedStatement.getResultSet(); /*獲取返回結(jié)果的數(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) { /*獲取列名的別名,如果沒(mé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ù)庫(kù)中單行數(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)建一個(gè)與數(shù)據(jù)庫(kù)表t_fruit一一對(duì)應(yīng)的Fruit類
import lombok.*; /** * @author long * @date 2022/6/15 14:58 * @Description: 與數(shù)據(jù)庫(kù)中的t_fruit表一一對(duì)應(yīng)的水果類 */ @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)建一個(gè)接口(FruitDAO)來(lái)聲明對(duì)改數(shù)據(jù)庫(kù)的表中的操作的方法。
import java.util.List; public interface FruitDAO { /*獲取所有水果信息*/ List<Fruit> getAllFruit(); /*添加水果信息*/ Integer addFruit(Fruit fruit); /*獲取水果的種類的數(shù)量*/ Long getAllCount(); /*獲取最大價(jià)格*/ Integer getMaxPrice(); /*獲取最小價(jià)格*/ Integer getMinPrice(); Fruit getFruitById(Integer id); }
對(duì)上述接口(FruitDAO)的實(shí)現(xiàn)類(FruitDAOImpl)
/** * @author long * @date 2022/6/15 15:04 * @Description: 對(duì)數(shù)據(jù)庫(kù)中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); } }
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
mysql百萬(wàn)數(shù)據(jù)表加索引優(yōu)化的方法
在大數(shù)據(jù)時(shí)代,隨著數(shù)據(jù)量的快速增長(zhǎng),對(duì)數(shù)據(jù)庫(kù)的索引優(yōu)化變得尤為重要,本文主要介紹了mysql百萬(wàn)數(shù)據(jù)表加索引優(yōu)化的方法,感興趣的可以了解一下2024-02-02MySQL動(dòng)態(tài)字符串處理DYNAMIC_STRING
本文主要給大家簡(jiǎn)單講解了mysql如何使用DYNAMIC_STRING來(lái)進(jìn)行動(dòng)態(tài)字符串的保存,非常的實(shí)用,有需要的小伙伴可以參考下2016-10-10MySQL數(shù)據(jù)庫(kù)優(yōu)化推薦的編譯安裝參數(shù)小結(jié)
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)優(yōu)化推薦的編譯安裝參數(shù)小結(jié),需要的朋友可以參考下2015-04-04