使用jdbc连接mysql数据库的几个步骤：
1.加载驱动类;
2.与数据库建立连接；
3.执行SQL语句
4.处理结果集
5.关闭连接
DB.java
package com.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
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.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
public class DB {
public static void main(String[] args){
}
public static Connection conn = null;
public PreparedStatement pst = null;
public String configFile = "DB.properties";
public DB(String configFile){
// this.configFile = configFile;
}
public Connection openConnection(){
String url = null;
String username = null;
String passwd = null;
String driver = null;
try {
String filePath = this.getClass().getResource("/").getPath()+this.configFile;
File directory = new File(filePath);
InputStream inStream = new FileInputStream(directory);
Properties prop = new Properties();
try {
prop.load(inStream);
} catch (IOException e) {
e.printStackTrace();
}
url = prop.getProperty("url");
username = prop.getProperty("username");
passwd = prop.getProperty("passwd");
driver = prop.getProperty("driver");
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
try {
Class.forName(driver);
System.out.println("Successfully loaded the MySQL driver");
try {
Connection conn = (Connection) DriverManager.getConnection(url,username,passwd);
DB.conn = conn;
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
/**
* 查询语句
* @param sql
* @return
*/
public List<Map<String, Object>> query(String sql) {
Connection conn = this.openConnection();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
try {
Statement stmt = (Statement) conn.createStatement();
ResultSet res = stmt.executeQuery(sql);
ResultSetMetaData rsmd = res.getMetaData();
int columCount = rsmd.getColumnCount();
while (res.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for(int i=1; i<=columCount; i++){
String name = rsmd.getColumnName(i);
Object value = res.getObject(name);
map.put(name, value);
}
list.add(map);
}
res.close();
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally{
this.close();
}
return null;
}
/**
* 增删改语句的方法
*
* @param sql
* sql语句
* @param data
* 执行参数
* @return 执行结果
* @throws SQLException
*/
public int[] exec(String sql, List<List<String>> data)
throws SQLException {
int[] result = new int[]{};
Connection conn = this.openConnection();
pst = conn.prepareStatement(sql);
conn.setAutoCommit(false);
if (data == null || data.isEmpty()) {
return result;
}
try {
int itemSize = 0;
Iterator<List<String>> res = data.iterator();
while (res.hasNext()) {
List<String> item = res.next();
if(itemSize == 0){
itemSize = item.size();
}
for(int i = 0; i < itemSize; i++){
pst.setString(i+1, item.get(i));
System.out.println(i+1+":"+item.get(i));
}
System.out.println(item.toString());
pst.addBatch();
}
result = pst.executeBatch();
conn.commit();
System.out.println("commit SQL:"+sql);
} catch (SQLException e) {
conn.rollback();
System.out.println("rollback SQL:"+sql);
e.printStackTrace();
} finally{
this.close();
}
System.out.println(result);
return result;
}
public void close(){
if(null != DB.conn)
try {
System.out.println("close the MySQL driver");
DB.conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
DB.properties
url=jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8
username=root
passwd=root
driver=com.mysql.jdbc.Driver调用示例：DataClean.java
package com;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.util.DB;
public class DataClean {
/**
* @param args
*/
public static void main(String[] args){
DataClean dataClean = new DataClean();
//dataClean.run();
Thread t = new Thread();
}
public void run(){
DB db = new DB("DB.properties");
DataClean dataClean = new DataClean();
String sql = "SELECT * FROM `test`.`hotel_data` where id between 1 and 100 LIMIT 100";
List<Map<String, Object>> list = db.query(sql);
Iterator<Map<String, Object>> it = list.iterator();
// sql = "INSERT INTO `test`.`hotel_data_clean` ( `Name`, `CardNo`, `Descriot`, `CtfTp`, `CtfId`, `Gender`, `Birthday`, `Address`, `Zip`, `Dirty`, `District1`, `District2`, `District3`, `District4`, `District5`, `District6`, `FirstNm`, `LastNm`, `Duty`, `Mobile`, `Tel`, `Fax`, `EMail`, `Nation`, `Taste`, `Education`, `Company`, `CTel`, `CAddress`, `CZip`, `Family`, `Version`) VALUES ('2', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ";
sql = "INSERT INTO `test`.`hotel_data_clean` (`Name`,`Birthday`,`Version`,`Mobile`,`CtfId`,`CtfTp`,`Gender`, `Address`, `Zip`, `Dirty`, `District1`, `District2`, `District3`, `District4`, `District5`, `District6`, `FirstNm`, `LastNm`, `Duty`, `Tel`, `Fax`, `EMail`, `Nation`, `Taste`, `Education`, `Company`, `CTel`, `CAddress`, `CZip`, `Family`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); ";
List<List<String>>data = new ArrayList<List<String>>();
while(it.hasNext()){
Map<String, Object> map = it.next();
String id = map.get("id").toString();
String Name = map.get("Name").toString();
String Version = map.get("Version").toString();
String Birthday = map.get("Birthday").toString();
String Mobile = map.get("Mobile").toString();
String CtfId = map.get("CtfId").toString();
String CtfTp = map.get("CtfTp").toString();
String Gender = map.get("Gender").toString();
String Address = map.get("Address").toString();
String Zip = map.get("Zip").toString();
String Dirty = map.get("Dirty").toString();
String District1 = map.get("District1").toString();
String District2 = map.get("District2").toString();
String District3 = map.get("District3").toString();
String District4 = map.get("District4").toString();
String District5 = map.get("District5").toString();
String District6 = map.get("District6").toString();
String FirstNm = map.get("FirstNm").toString();
String LastNm = map.get("LastNm").toString();
String Duty = map.get("Duty").toString();
String Tel = map.get("Tel").toString();
String Fax = map.get("Fax").toString();
String EMail = map.get("EMail").toString();
String Nation = map.get("Nation").toString();
String Taste = map.get("Taste").toString();
String Education = map.get("Education").toString();
String Company = map.get("Company").toString();
String CTel = map.get("District3").toString();
String CAddress = map.get("District4").toString();
String CZip = map.get("District5").toString();
String Family = map.get("District6").toString();
System.out.println("未处理 id"+id+",Name:"+Name+",Version:"+Version+",Birthday:"+Birthday+",Mobile:"+Mobile);
Name = dataClean.NameFormat(Name);
Version = dataClean.VersionFormat(Version);
Birthday = dataClean.BirthdayFormat(Birthday);
Mobile = dataClean.MobileFormat(Mobile);
CtfId = dataClean.NameFormat(CtfId);
CtfTp = dataClean.NameFormat(CtfTp);
CtfTp = dataClean.NameFormat(CtfTp);
Gender = dataClean.NameFormat(Gender);
Address = dataClean.NameFormat(Address);
Zip = dataClean.DistrictFormat(Zip);
Dirty = dataClean.NameFormat(Dirty);
District1 = dataClean.DistrictFormat(District1);
District2 =dataClean.DistrictFormat(District2);
District3 = dataClean.DistrictFormat(District3);
District4 = dataClean.DistrictFormat(District4);
District5 = dataClean.DistrictFormat(District5);
District6 =dataClean.DistrictFormat(District6);
FirstNm = dataClean.NameFormat(FirstNm);
LastNm = dataClean.NameFormat(LastNm);
Duty = dataClean.NameFormat(Duty);
Tel = dataClean.NameFormat(Tel);
Fax = dataClean.NameFormat(Fax);
EMail = dataClean.NameFormat(EMail);
Nation = dataClean.NameFormat(Nation);
Taste = dataClean.NameFormat(Taste);
Education =dataClean.NameFormat(Education);
Company = dataClean.NameFormat(Company);
CTel = dataClean.NameFormat(CTel);
CAddress = dataClean.NameFormat(CAddress);
CZip = dataClean.DistrictFormat(CZip);
Family = dataClean.NameFormat(Family);
System.out.println("已处理 id"+id+",Name:"+Name+",Version:"+Version+",Birthday:"+Birthday+",Mobile:"+Mobile);
// System.out.println(map.values());
// Map<String, Object> newList = new HashMap<String, Object>();
List<String> newList = new ArrayList<String>();
newList.add(Name);
newList.add(Birthday);
newList.add(Version);
newList.add(Mobile);
newList.add(CtfId);
newList.add(CtfTp);
newList.add(Gender);
newList.add(Address);
newList.add(Zip);
newList.add(Dirty);
newList.add(District1);
newList.add(District2);
newList.add(District3);
newList.add(District4);
newList.add(District5);
newList.add(District6);
newList.add(FirstNm);
newList.add(LastNm);
newList.add(Duty);
newList.add(Tel);
newList.add(Fax);
newList.add(EMail);
newList.add(Nation);
newList.add(Taste);
newList.add(Education);
newList.add(Company);
newList.add(CTel);
newList.add(CAddress);
newList.add(CZip);
newList.add(Family);
data.add(newList);
}
try {
db.exec(sql, data);
} catch (SQLException e) {
System.out.println("exception:"+data.toString());
e.printStackTrace();
}
}
/**
* 处理姓名
* @param Name
* @return
*/
public String NameFormat(String Name){
Name = Name.trim();
if(Name.equals("")){
Name = "";
}
return Name;
}
/**
* 处理日期
* @param Version
* @return
*/
public String VersionFormat(String Version){
if(Version.equals("")) return "0000-00-00 00:00:00";
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date date = dateFormat.parse(Version);
Version = dateFormat.format(date).toString();
} catch (ParseException e) {
e.printStackTrace();
}
return Version;
}
public String BirthdayFormat(String birthday){
birthday = birthday.trim();
if(birthday.indexOf("#") > -1){
birthday = birthday.replace("#", "");
}
int birthdayInt = Integer.parseInt(birthday);
return String.valueOf(birthdayInt);
}
public String MobileFormat(String Mobile){
Mobile = this.GetNum(Mobile);
Pattern p = Pattern.compile("^((13\\d{9}$)|(15[0,1,2,3,5,6,7,8,9]\\d{8}$)|(18[0,2,5,6,7,8,9]\\d{8}$)|(147\\d{8})$)");
Matcher m = p.matcher(Mobile);
if(m.matches()){
return Mobile;
}
return "";
}
public String DistrictFormat(String District){
District = this.GetNum(District);
if(District.equals(""))
return "0";
else {
return District;
}
}
/**
* 提取数字
* @param str
* @return
*/
public String GetNum(String str){
Pattern pattern = Pattern.compile("[^0-9]");
Matcher matcher = pattern.matcher(str);
String all = matcher.replaceAll("");
System.out.println("number:" + all);
return all;
}
}