JdbcUtil.java
package com.hb.media;
import java.io.InputStream;
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;
import java.util.Properties;
public class JdbcUtil {
// 表示定义数据库的用户名
private static String USERNAME ;
// 定义数据库的密码
private static String PASSWORD;
// 定义数据库的驱动信息
private static String DRIVER;
// 定义访问数据库的地址
private static String URL;
// 定义数据库的链接
private Connection connection;
// 定义sql语句的执行对象
private PreparedStatement pstmt;
// 定义查询返回的结果集合
private ResultSet resultSet;
static{
//加载数据库配置信息,并给相关的属性赋值
loadConfig();
}
/**
* 加载数据库配置信息,并给相关的属性赋值
*/
public static void loadConfig() {
try {
InputStream inStream = JdbcUtil.class
.getResourceAsStream("/jdbc.properties");
Properties prop = new Properties();
prop.load(inStream);
USERNAME = prop.getProperty("jdbc.username");
PASSWORD = prop.getProperty("jdbc.password");
DRIVER= prop.getProperty("jdbc.driver");
URL = prop.getProperty("jdbc.url");
} catch (Exception e) {
throw new RuntimeException("读取数据库配置文件异常!", e);
}
}
public JdbcUtil() {
}
/**
* 获取数据库连接
*
* @return 数据库连接
*/
public Connection getConnection() {
try {
Class.forName(DRIVER); // 注册驱动
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 获取连接
} catch (Exception e) {
throw new RuntimeException("get connection error!", e);
}
return connection;
}
/**
* 执行更新操作
*
* @param sql
* sql语句
* @param params
* 执行参数
* @return 执行结果
* @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List<?> params)
throws SQLException {
boolean flag = false;
int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
pstmt = connection.prepareStatement(sql);
int index = 1;
// 填充sql语句中的占位符
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;
}
/**
* 执行查询操作
*
* @param sql
* sql语句
* @param params
* 执行参数
* @return
* @throws SQLException
*/
public List<Map<String, Object>> findResult(String sql, List<?> params)
throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
/**
* 释放资源
*/
public void releaseConn() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
JdbcUtil jdbcUtil = new JdbcUtil();
jdbcUtil.getConnection();
try {
List<Map<String, Object>> result = jdbcUtil.findResult(
"select * from t_student", null);
for (Map<String, Object> m : result) {
System.out.println(m);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
jdbcUtil.releaseConn();
}
}
}
jdbc.properties
jdbc.username=root
jdbc.password=123
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://10.10.10.10:3306/bunny?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8
Update_b_base_media.java
package com.hb.media;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
public class Update_b_base_media {
private BufferedReader br;
/**
* 更新用户信息(有则更新,没有则插入)
*/
public void updatedMedia() {
JdbcUtil jdbcUtil = null;
boolean bool = false;
String itunesId = "";
String bundleId = "";
int categoryId = 0;
String zhName = "";
int id = 0;
String sql = "";
int n=0;
try {
/* 读入TXT文件 */
String pathname = "/Users/hebin/Desktop/update-media"; // 绝对路径或相对路径都可以,这里是绝对路径,写入文件时演示相对路径
File filename = new File(pathname); // 要读取以上路径的input。txt文件
InputStreamReader reader = new InputStreamReader(new FileInputStream(filename)); // 建立一个输入流对象reader
br = new BufferedReader(reader);
String line = "";
line = br.readLine();
while (line != null) {
String[] split = line.split("\t");
System.out.println(line);
itunesId = isNumeric(split[0]) ? split[0] : "";
bundleId = isNumeric(split[0]) ? "" : split[0];
categoryId = Integer.parseInt(split[1]);
zhName = split[2];
id = findMediaById("".equals(itunesId) ? bundleId : itunesId);
// 创建填充参数的list
List<Object> paramList = new ArrayList<Object>();
// 填充参数
paramList.add(itunesId);
paramList.add(bundleId);
paramList.add(categoryId);
paramList.add(zhName);
if (id == 0) {
sql = "insert into b_base_media (itunesId,bundleId,categoryId,zhName,enName,rank,status) values (?,?,?,?,'',1,1);";
} else {
paramList.add(id);
sql = "update b_base_media set itunesId=?,bundleId=?,categoryId=?,zhName=? where id=?;";
}
jdbcUtil = new JdbcUtil();
jdbcUtil.getConnection(); // 获取数据库链接
bool = jdbcUtil.updateByPreparedStatement(sql, paramList);
n++;
line = br.readLine(); // 一次读入一行数据
}
System.out.println(n);
} catch (Exception e) {
System.out.println(this.getClass() + "执行更新操作抛出异常!");
e.printStackTrace();
} finally {
if (jdbcUtil != null) {
jdbcUtil.releaseConn(); // 一定要释放资源
}
}
System.out.println("执行更新的结果:" + bool);
}
/**
* 根据b_base_meida中的自增ID
*/
public int findMediaById(String app_id) {
int id = 0;
String sql = isNumeric(app_id) ? "select * from b_base_media where itunesId = ?"
: "select * from b_base_media where bundleId = ?";
// 创建填充参数的list
List<Object> paramList = new ArrayList<Object>();
// 填充参数
paramList.add(app_id);
JdbcUtil jdbcUtil = null;
try {
jdbcUtil = new JdbcUtil();
jdbcUtil.getConnection(); // 获取数据库链接
List<Map<String, Object>> mapList = jdbcUtil.findResult(sql.toString(), paramList);
if (mapList.size() == 1) {
Map<String, Object> map = mapList.get(0);
id = Integer.parseInt("" + map.get("id"));
}
} catch (SQLException e) {
System.out.println(this.getClass() + "执行查询操作抛出异常!");
e.printStackTrace();
} finally {
if (jdbcUtil != null) {
jdbcUtil.releaseConn(); // 一定要释放资源
}
}
return id;
}
public static boolean isNumeric(String str) {
Pattern pattern = Pattern.compile("[0-9]*");
return pattern.matcher(str).matches();
}
public static void main(String[] args) {
// System.out.println(new
// Update_b_base_media().findMediaById("983522106"));
new Update_b_base_media().updatedMedia();
// System.out.println(isNumeric("5559"));
}
}