java操作mysql的JDBC

电玩女神 2024-02-17 22:36 55阅读 0赞

1.mysql\_jdbc.properties配置文件

mysql\_url=jdbc:mysql://192.168.1.2:3306/test  
mysql\_username=test  
mysql\_password=test

2.JDBCDBUtils .java

/\*\*  
\*  
\*/  
package com.dh.imsi.utils;  
  
  
  
  
import java.io.File;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.ResultSetMetaData;  
import java.util.ArrayList;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
  
/\*\*  
\* @title : DBUtils.java  
  
\* @author : tao  
\* @date : 2017-6-8 上午9:49:46  
\* @version V1.0  
\*/  
public class JDBCDBUtils \{  
  
  
private static String url = "jdbc:mysql://127.0.0.1/test";  
private static String driverName = "com.mysql.jdbc.Driver";  
private static String user = "root";  
private static String password = "root";  
  
  
private Connection conn; // 数据库连接  
private PreparedStatement preStat; // 预编译  
private ResultSet rstSet; // 结果集  
  
static \{  
try \{  
String path = System.getProperty("user.dir");  
String confPath = path + File.separator + "bin" + File.separator  
\+ "mysql\_jdbc.properties";  
Map<String, String> map = PropertiesUtils.readProperties(confPath);  
if (map != null && map.size() > 0) \{  
url = CommonFunc.checkNull(map.get("mysql\_url"));  
user = CommonFunc.checkNull(map.get("mysql\_username"));  
password = CommonFunc.checkNull(map.get("mysql\_password"));  
\}  
Class.forName(driverName);//指定连接类型  
\} catch (Exception e) \{  
System.out.println("加载数据库配置文件时异常, e = " + e);  
e.printStackTrace();  
\}  
\}  
  
/\*\*  
\* 创建连接  
\*/  
private void createConn() \{  
try \{  
conn = DriverManager.getConnection(url, user, password);  
\} catch (Exception e) \{  
System.out.println("创建Connection时异常, e = " + e);  
e.printStackTrace();  
\}  
\}  
  
/\*\*  
\* 释放  
\* @param flag  
\*/  
public void freeAll(boolean flag) \{  
try \{  
if (flag && conn != null) \{  
conn.commit();  
\} else if (conn != null) \{  
conn.rollback();  
\}  
if (rstSet != null) \{  
rstSet.close();  
rstSet = null;  
\}  
if (preStat != null) \{  
preStat.close();  
preStat = null;  
\}  
if (conn != null) \{  
conn.close();  
conn = null;  
\}  
\} catch (Exception exception1) \{  
\}  
\}  
  
/\*\*  
\* 查询  
\* @param sql  
\* @param values  
\* @return  
\*/  
public List<Map<String, Object>> query(String sql, Object\[\] values) \{  
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();  
try \{  
if (conn == null) \{  
createConn();  
\}  
// 初始化PreparedStatement  
preStat = conn.prepareStatement(sql);  
for (int i = 0; i < values.length; i++) \{  
preStat.setObject((i + 1), values\[i\]);  
\}  
rstSet = preStat.executeQuery();  
/\*  
\* 遍历结果集  
\*/  
if (rstSet != null) \{  
ResultSetMetaData rsmd = rstSet.getMetaData();  
int columnCount = rsmd.getColumnCount(); // 列数  
while (rstSet.next()) \{  
Map<String, Object> map = new HashMap<String, Object>();  
/\*  
\* 根据列数遍历  
\*/  
for (int j = 1; j < columnCount + 1; j++) \{  
Object obj = rstSet.getObject(j);  
if (obj != null) \{  
map.put(rsmd.getColumnName(j).toLowerCase(), obj);  
\} else \{  
map.put(rsmd.getColumnName(j).toLowerCase(), null);  
\}  
\}  
list.add(map);  
\}  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\} finally \{  
freeAll(false);  
\}  
return list;  
\}  
  
public static void main(String\[\] args)\{  
try \{  
/\*Properties pro = new Properties();  
InputStream in = DBUtils.class.getResourceAsStream("/mysql\_jdbc.properties");  
pro.load(in);  
in.close();  
  
String val = PropertiesUtils.readValue(pro, "mysql\_url");  
System.out.println("val = " + val);\*/  
/\*String path = System.getProperty("user.dir");  
String confPath = path + File.separator + "bin" + File.separator + "mysql\_jdbc.properties";  
//System.out.println("confPath = " + confPath);  
  
Map<String, String> map = PropertiesUtils.readProperties(confPath);  
Iterator<Entry<String, String>> it = map.entrySet().iterator();  
while(it.hasNext())\{  
Map.Entry<String, String> entry = it.next();  
System.out.println(entry.getKey() + " = " + entry.getValue());  
\}\*/  
  
JDBCDBUtils dbUtils = new JDBCDBUtils();  
String sql = "select \* from sys\_user where USERACC=? ";  
Object\[\] values = new Object\[\]\{"00000000"\};  
List<Map<String, Object>> list = dbUtils.query(sql, values);  
Map<String, Object> map = list.get(0);  
System.out.println("useracc = " + CommonFunc.checkNull(map.get("useracc")));  
  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
\}  
\}

3. CommonFunc .java

/\*\*  
\*  
\*/  
package com.dh.imsi.utils;  
  
  
/\*\*  
\* @title : CommonFunc.java  
  
\* @author : tao  
\* @date : 2017-6-8 上午10:05:18  
\* @version V1.0  
\*/  
public class CommonFunc \{  
  
  
/\*\*  
\* 判空字符串  
\*  
\* @param obj  
\* @return  
\*/  
public static String checkNull(Object obj) \{  
if (obj == null) \{  
return "";  
\}  
return obj.toString().trim();  
\}  
  
/\*\*  
\* 判断字符串是否为大于等于0的整数  
\* @param str 待操作的字符串  
\* @param defaultVal 默认值  
\* @return  
\*/  
public static int checkNum(String str, int defaultVal)\{  
if("".equals(checkNull(str)))\{  
return defaultVal;  
\}  
int rstVal = defaultVal;  
String pattern = "^\[0-9\]+$"; // 匹配大于等于0的整数  
if(str.matches(pattern))\{  
rstVal = Integer.parseInt(str);  
\}  
return rstVal;  
\}  
  
public static void main(String\[\] args)\{  
String str = "a123sf";  
System.out.println("str == " + checkNum(str, -1));  
\}  
  
  
\}

4.PropertiesUtils.java

/\*\*  
\*  
\*/  
package com.dh.imsi.utils;  
  
  
import java.io.BufferedInputStream;  
import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.io.OutputStream;  
import java.util.Enumeration;  
import java.util.HashMap;  
import java.util.Iterator;  
import java.util.Map;  
import java.util.Properties;  
import java.util.Set;  
  
  
  
  
/\*\*  
\* @title : PropertiesUtils.java  
  
\* @author : tao  
\* @date : 2017-6-8 上午10:03:22  
\* @version V1.0  
\*/  
public class PropertiesUtils \{  
  
  
/\*\*  
\* 读取Properties文件中key的值  
\* @param filePath  
\* @param key  
\* @return  
\*/  
public static String readValue(String filePath, String key) \{  
Properties props = new Properties();  
String value = "";  
try \{  
File file = new File(filePath);  
if (file.exists()) \{  
InputStream in = new BufferedInputStream(new FileInputStream(  
filePath));  
props.load(in);  
value = props.getProperty(key);  
// System.out.println(key+"="+value);  
in.close(); // 关闭流  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
return value;  
\}  
  
  
/\*\*  
\* 读取Properties文件中key的值  
\* @param props  
\* @param key  
\* @return  
\*/  
public static String readValue(Properties props, String key) \{  
String value = "";  
try \{  
if (props != null) \{  
value = CommonFunc.checkNull(props.getProperty(key));  
// System.out.println(key+"="+value);  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
return value;  
\}  
  
  
/\*\*  
\*  
\* @param filePath  
\* @return  
\*/  
@SuppressWarnings("rawtypes")  
public static Map<String, String> readProperties(String filePath) \{  
Properties props = null;  
HashMap<String, String> map = new HashMap<String, String>();  
try \{  
File file = new File(filePath);  
if (file.exists()) \{  
props = new Properties();  
InputStream in = new BufferedInputStream(new FileInputStream(  
filePath));  
props.load(in);  
Enumeration en = props.propertyNames();  
while (en.hasMoreElements()) \{  
String key = (String) en.nextElement();  
String value = props.getProperty(key);  
map.put(key, CommonFunc.checkNull(value));  
// System.out.println(key+Property);  
\}  
in.close();  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
return map;  
\}  
  
  
/\*\*  
\* 向Properties配置文件中写数据  
\* @param filePath  
\* @param map  
\*/  
public static void writeProperties(String filePath,  
HashMap<String, String> map) \{  
Properties prop = new Properties();  
Set<Map.Entry<String, String>> set = map.entrySet();  
Iterator<Map.Entry<String, String>> it = set.iterator();  
try \{  
InputStream fis = new FileInputStream(filePath);  
prop.load(fis);  
OutputStream fos = new FileOutputStream(filePath);  
while (it.hasNext()) \{  
Map.Entry<String, String> me = it.next();  
prop.setProperty(me.getKey(), me.getValue());  
prop.store(fos, "Update '" + me.getKey() + "' value");  
\}  
\} catch (IOException e) \{  
// System.err.println("Visit "+filePath+" for updating "+parameterName+" value error");  
\}  
\}  
  
  
\}