您好,登錄后才能下訂單哦!
使用java怎么連接數(shù)據(jù)庫并實現(xiàn)增刪改查操作?相信很多沒有經(jīng)驗的人對此束手無策,為此本文總結了問題出現(xiàn)的原因和解決方法,通過這篇文章希望你能解決這個問題。
1、設置數(shù)據(jù)庫的基本配置信息
package mysql; //數(shù)據(jù)庫配置資源連接 public final class DbConfig { //數(shù)據(jù)庫資源 指定所使用的數(shù)據(jù)庫類型 地址 端口 數(shù)據(jù)庫名稱 編碼格式 public final static String dbUrl = "jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncodong=utf-8"; //數(shù)據(jù)庫用戶名 public final static String dbUser= "xxxxxx"; //數(shù)據(jù)庫密碼 public final static String dbPwd = "xxxxxx"; }
2、數(shù)據(jù)庫連接類 用戶獲取數(shù)據(jù)庫連接
package mysql; import java.sql.SQLException; import java.sql.Connection; import java.sql.DriverManager; public class Conn { //保存住默認數(shù)據(jù)庫連接 private static Connection conn = null; //數(shù)據(jù)庫連接 private static Connection getDbConn(String dbUurl,String dbUser,String dbPwd) { Connection dbConn; try{ //載入mysql 工具包 Class.forName("com.mysql.jdbc.Driver"); dbConn = DriverManager.getConnection(dbUurl,dbUser,dbPwd); }catch(ClassNotFoundException | SQLException e){ dbConn = null; e.printStackTrace(); } return dbConn; } //獲得第三方數(shù)據(jù)庫鏈接 public static Connection getConn(String dbUrl,String dbUser,String dbPwd) { return getDbConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd); } //獲得本地默認數(shù)據(jù)庫連接 public static Connection getConn() { if(conn == null){ conn = getDbConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd); } return conn; } }
3、數(shù)據(jù)庫測試類 檢驗獲得的數(shù)據(jù)庫連接是否有效
import Demo.Demo; import mysql.*; import java.sql.Connection; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.PreparedStatement; public class Index { public static void main(String[] args) { index1(); //分隔符 System.out.println("----------------------------------------------"); System.out.println("----------------------------------------------"); System.out.println("----------------------------------------------"); index2(); } //自定義數(shù)據(jù)庫連接方式 public static void index1() { //獲取數(shù)據(jù)庫連接 Connection conn = Conn.getConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd); //測試該數(shù)據(jù)庫連接是否有效 index3(conn); } //默認方式獲取數(shù)據(jù)庫連接 public static void index2() { //獲取數(shù)據(jù)庫連接 Connection conn = Conn.getConn(); //測試該數(shù)據(jù)庫連接是否有效 index3(conn); } /** * 測試數(shù)據(jù)庫連接是否有效 * @param * Connection conn 數(shù)據(jù)庫連接 * */ public static void index3(Connection conn) { //定義要執(zhí)行的sql語句 String sql = "select * from table_name where id = ?"; try { //對sql語句進行預處理 PreparedStatement pre = conn.prepareStatement(sql); //變量數(shù)據(jù)填充 填充后的sql為 select * from table_name where id = "xxxxxxx" pre.setString(1,"xxxxx"); //執(zhí)行sql語句 發(fā)揮執(zhí)行的結果 ResultSet result = pre.executeQuery(); //返回的結果是否是空數(shù)據(jù) if(!result.next()) { System.out.println("沒有查詢到響應的數(shù)據(jù)"); return; } //獲取返回結果的元數(shù)據(jù),列名 ResultSetMetaData meta = result.getMetaData(); //打印輸出 int metaLength = meta.getColumnCount(); do{ for(int forInt = 1;forInt <= metaLength;forInt++) { String keyName = meta.getColumnName(forInt); System.out.println(keyName + " => " + result.getString(keyName)); } }while(result.next()); }catch(SQLException e){ e.printStackTrace(); } } }
獲取到Connection 連接后看到執(zhí)行一個sql語句獲得返回結果還要這么多的處理操作,以下是自己封裝的mysql操作類
數(shù)據(jù)庫接口類,針對數(shù)據(jù)庫操作的類,都得實現(xiàn)這個類。不管以后更換了什么數(shù)據(jù)庫,該數(shù)據(jù)庫的操作類都得實現(xiàn)這個接口所規(guī)定的方法,然后我們不需要任何的改動,只需要變更該接口的實現(xiàn)就可以了。
package standard.db.operation; import java.sql.Connection; import java.util.List; import java.util.Map; import spring.beans.db.realization.mysql.DataType; public interface DbOper { /** * 數(shù)據(jù)庫連接 * */ public void setConnection(Connection conn); public Connection getConnection(); /** * 查詢所有數(shù)據(jù) * @param * String 要執(zhí)行的sql語句 * @param * String[] keyVal 映射數(shù)組 * @return * List<Map<String,String>> result 查詢結果集 * */ public List<Map<String,String>> query(String sql,String[] keyVal); public List<Map<String,String>> query(String sql); /** * 查詢單行數(shù)據(jù) * @param * String 要執(zhí)行的sql語句 * @param * String[] keyVal 映射數(shù)組 * @return * Map<String,String> result 查詢結果集 * */ public Map<String,String> find(String sql,String[] keyVal); public Map<String,String> find(String sql); /** * 更新數(shù)據(jù) * @param * String 要執(zhí)行的sql語句 * @param * String[] keyVal 映射數(shù)組 * @return * int resultInt 受影響的行數(shù) * */ public int update(String sql,String[] keyVal); public int update(String sql); /** * 新增數(shù)據(jù) * @param * String 要執(zhí)行的sql語句 * @param * String[] keyVal 映射數(shù)組 * @return * int resultInt 新增成功行數(shù) * */ public int insert(String sql,String[] keyVal); public int insert(String sql); /** * 刪除數(shù)據(jù)庫 * @param * String 要執(zhí)行的sql語句 * @param * String[] keyVal 映射數(shù)組 * @return * boolean 刪除時候成功 * */ public boolean delete(String sql,String[] keyVal); public boolean delete(String sql); /** * 調用存儲過程 * @param * String callFunc 存儲過程名稱 * List<Map<String,String>> 存儲過程參數(shù)值 如:Map<"int","22"> * */ public List<Map<String,String>> callResult(String callFunc,List<DataType> keyVal); public List<Map<String,String>> callResult(String callFunc); }
針對DbOper接口的實現(xiàn)
package spring.beans.db.realization.mysql; import java.sql.Connection; import java.util.List; import java.util.ArrayList; import java.util.Map; import java.util.HashMap; import java.sql.CallableStatement; import java.sql.SQLException; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import javax.annotation.PostConstruct; import javax.annotation.PreDestroy; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Repository; import standard.db.operation.DbOper; /** * mysql 操作實現(xiàn)類 - 容器 * author : 譚勇 * create_date : 2017-04-13 * */ @Repository("db_connection") @Scope("request") public final class MysqlRealization implements DbOper { private Connection conn; /** * 對象初始化 * */ @PostConstruct public void initDb() { } @Override public void setConnection(Connection conn) { this.conn = conn; } @Value(value="#{mysql_driver_manager_dataSource}") public void setConnection(DataSource dataSource) { try{ setConnection(dataSource.getConnection()); }catch(SQLException e) { } } @Override public Connection getConnection() { return this.conn; } @Override public List<Map<String, String>> query(String sql, String[] keyVal) { PreparedStatement pre = null; ResultSet result = null; ResultSetMetaData meta = null; try{ pre = conn.prepareStatement(sql); if(keyVal != null) { //映射到問號 for(int i=1;i<=keyVal.length;i++) { pre.setString(i, keyVal[i-1]); } } result = pre.executeQuery(); if (result.next()) { meta = result.getMetaData(); result.last(); List<Map<String,String>> list = new ArrayList<Map<String,String>>(result.getRow()); result.first(); int propertiesLength = meta.getColumnCount(); do{ Map<String,String> map = new HashMap<String,String>(propertiesLength); for(int i=1;i<=propertiesLength;i++) { String keyName = meta.getColumnName(i); map.put(keyName, result.getString(keyName)); } list.add(map); }while(result.next()); return list; } }catch(SQLException e) { e.printStackTrace(); }finally{ closePreparedStatement(pre); closeResultSet(result); } return null; } @Override public List<Map<String, String>> query(String sql) { return query(sql,null); } @Override public Map<String, String> find(String sql, String[] keyVal) { PreparedStatement pre = null; ResultSet result = null; ResultSetMetaData meta = null; try{ pre = conn.prepareStatement(sql); if(keyVal != null) { //映射到問號 for(int i=1;i<=keyVal.length;i++) { pre.setString(i, keyVal[i-1]); } } result = pre.executeQuery(); if (result.next()) { meta = result.getMetaData(); int propertiesLength = meta.getColumnCount(); Map<String,String> map = new HashMap<String,String>(propertiesLength); for(int i=1;i<=propertiesLength;i++) { String keyName = meta.getColumnName(i); map.put(keyName, result.getString(keyName)); } return map; } }catch(SQLException e) { e.printStackTrace(); }finally{ closePreparedStatement(pre); closeResultSet(result); } return null; } @Override public Map<String, String> find(String sql) { return find(sql,null); } @Override public int update(String sql, String[] keyVal) { PreparedStatement pre = null; try{ pre = conn.prepareStatement(sql); if(keyVal != null) { //映射到問號 for(int i=1;i<=keyVal.length;i++) { pre.setString(i, keyVal[i-1]); } } return pre.executeUpdate(); }catch(SQLException e) { e.printStackTrace(); }finally{ closePreparedStatement(pre); } return 0; } @Override public int update(String sql) { return update(sql,null); } @Override public int insert(String sql, String[] keyVal) { PreparedStatement pre = null; try{ pre = conn.prepareStatement(sql); if(keyVal != null) { //映射到問號 for(int i=1;i<=keyVal.length;i++) { pre.setString(i, keyVal[i-1]); } } return pre.executeUpdate(); }catch(SQLException e) { e.printStackTrace(); }finally{ closePreparedStatement(pre); } return 0; } @Override public int insert(String sql) { return insert(sql,null); } @Override public boolean delete(String sql, String[] keyVal) { PreparedStatement pre = null; try{ pre = conn.prepareStatement(sql); if(keyVal != null) { //映射到問號 for(int i=1;i<=keyVal.length;i++) { pre.setString(i, keyVal[i-1]); } } return pre.executeUpdate() > 0 ? true:false; }catch(SQLException e) { e.printStackTrace(); }finally{ closePreparedStatement(pre); } return false; } @Override public boolean delete(String sql) { return delete(sql,null); } /** * 調用存儲過程 * @param * String callFunc 存儲過程名 * */ public List<Map<String,String>> callResult(String callFunc,List<DataType> keyVal) { String call = "{call " + callFunc + "}"; ResultSetMetaData meta = null; CallableStatement callableStatement= null; ResultSet result = null; try{ callableStatement = conn.prepareCall(call); if(keyVal != null) { for(int i=1;i<=keyVal.size();i++) { DataType data = keyVal.get(i-1); switch(data.getType()) { case ValueTypeSource.STRING: callableStatement.setString(i, String.valueOf(data.getValue())); break; case ValueTypeSource.INT: callableStatement.setInt(i, Integer.valueOf(data.getValue())); break; case ValueTypeSource.LONG: callableStatement.setLong(i, Long.valueOf(data.getValue())); break; case ValueTypeSource.DOUBLE: callableStatement.setDouble(i, Double.valueOf(data.getValue())); break; default: callableStatement.setString(i,String.valueOf(data.getValue())); } } } callableStatement.execute(); result = callableStatement.getResultSet(); meta = result.getMetaData(); result.last(); List<Map<String,String>> list = new ArrayList<Map<String,String>>(result.getRow()); result.first(); int propertiesLength = meta.getColumnCount(); do{ Map<String,String> map = new HashMap<String,String>(propertiesLength); for(int i=1;i<=propertiesLength;i++) { String keyName = meta.getColumnName(i); map.put(keyName, result.getString(keyName)); } list.add(map); }while(result.next()); return list; }catch(SQLException e) { e.printStackTrace(); return null; }finally{ closeCallableStatement(callableStatement); closeResultSet(result); } } @Override public List<Map<String,String>> callResult(String callFunc) { return callResult(callFunc,null); } /** * 關閉資源鏈接 * */ private void closePreparedStatement(PreparedStatement pre) { if(pre != null) { try { pre.close(); }catch(SQLException e) { e.printStackTrace(); } } } private void closeResultSet(ResultSet result) { if(result != null) { try { result.close(); }catch(SQLException e) { e.printStackTrace(); } } } private void closeCallableStatement(CallableStatement call) { if(call != null) { try { call.close(); }catch(SQLException e) { e.printStackTrace(); } } } private void closeConnection(Connection conn) { if(conn != null) { try { conn.close(); }catch(SQLException e) { e.printStackTrace(); } } } /** * 對象注銷 * */ @PreDestroy public void closeDb() { closeConnection(conn); } }
以下用于調用存儲過程使用的工具類
package spring.beans.db.realization.mysql; public final class DataType { private String keyName; private String value; private int type; public DataType(){} public DataType(String keyName,String value,int type) { setKeyName(keyName); setValue(value); setType(type); } public void setKeyName(String keyName) { this.keyName = keyName; } public void setValue(String value) { this.value = value; } public void setType(int type) { this.type = type; } public String getKeyName() { return keyName; } public String getValue() { return value; } public int getType() { return type; } }
package spring.beans.db.realization.mysql; public enum ValueType { INT(ValueTypeSource.INT), STRING(ValueTypeSource.STRING), DOUBLE(ValueTypeSource.DOUBLE), CHAR(ValueTypeSource.CHAR), DATE(ValueTypeSource.DATE), BLOB(ValueTypeSource.BLOB), LONG(ValueTypeSource.LONG); private int type; private ValueType(int type) { this.type = type; } public int getType() { return type; } }
package spring.beans.db.realization.mysql; public final class ValueTypeSource { public final static int INT=1, STRING=2, DOUBLE=3, CHAR=4, DATE=5, LONG=6, BLOB=7; }
1. 簡單,只需理解基本的概念,就可以編寫適合于各種情況的應用程序;2. 面向對象;3. 分布性,Java是面向網(wǎng)絡的語言;4. 魯棒性,java提供自動垃圾收集來進行內存管理,防止程序員在管理內存時容易產(chǎn)生的錯誤。;5. 安全性,用于網(wǎng)絡、分布環(huán)境下的Java必須防止病毒的入侵。6. 體系結構中立,只要安裝了Java運行時系統(tǒng),就可在任意處理器上運行。7. 可移植性,Java可以方便地移植到網(wǎng)絡上的不同機器。8.解釋執(zhí)行,Java解釋器直接對Java字節(jié)碼進行解釋執(zhí)行。
看完上述內容,你們掌握使用java怎么連接數(shù)據(jù)庫并實現(xiàn)增刪改查操作的方法了嗎?如果還想學到更多技能或想了解更多相關內容,歡迎關注億速云行業(yè)資訊頻道,感謝各位的閱讀!
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內容。