java操作mysql的JDBC

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");
}
}


}