Java JDBC更新mysql数据库

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