Java数据库操作

一、准备工作

首先是部署数据库。这里以MySql为例，到官网下载MySql服务器安装包。笔者是在Windows上面测试，所以下载的是可执行的安装包，安装完后数据库服务自动启动。

然后是下载数据库驱动，官网上称作MySql connectors for Java。

最后是在项目的Build Path里面加入刚才下载的驱动Jar包。

二、代码实例

package adam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBManager {
	// 用户名
	private String user = "";
	// 密码
	private String password = "";
	// 主机
	private String host = "";
	// 数据库名字
	private String database = "";
	/*
	 * 
	 * private String
	 * url="jdbc:mysql://"+host+"/"+"useUnicode=true&characterEncoding=GB2312";
	 */
	private String url = "";

	private Connection con = null;

	Statement stmt;
	/**
	 * 
	 * 根据主机、数据库名称、数据库用户名、数据库用户密码取得连接。
	 * 
	 * @param host
	 *            String
	 * 
	 * @param database
	 *            String
	 * 
	 * @param user
	 *            String
	 * 
	 * @param password
	 *            String
	 */
	public DBManager(String host, String database, String user, String password) {
		this.host = host;
		this.database = database;
		this.user = user;
		this.password = password;
		// 显示中文
		this.url = "jdbc:mysql://" + host + "/" + database
				+ "?useUnicode=true&characterEncoding=GB2312";
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
		}

		catch (ClassNotFoundException e) {
			System.err.println("class not found:" + e.getMessage());
		}

		try {
			con = DriverManager.getConnection(this.url, this.user,
					this.password);
			// 连接类型为ResultSet.TYPE_SCROLL_INSENSITIVE,
			// ResultSet.CONCUR_READ_ONLY
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		}
		catch (SQLException a) {
			System.err.println("sql exception:" + a.getMessage());
		}
	}

	/**
	 * 
	 * 返回取得的连接
	 */
	public Connection getCon() {
		return con;
	}

	/**
	 * 
	 * 执行一条简单的查询语句
	 * 
	 * 返回取得的结果集
	 */
	public ResultSet executeQuery(String sql) {
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(sql);
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	/**
	 * 执行一条简单的更新语句
	 * 执行成功则返回true
	 */
	@SuppressWarnings("finally")
	public boolean executeUpdate(String sql) {
		boolean v = false;
		try {
			v = stmt.executeUpdate(sql) > 0 ? true : false;
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			return v;
		}
	}
	
	/**
	 * 输出所有数据库记录
	 */
	public void outputDB(){
		ResultSet rs;
		rs = executeQuery("SELECT * FROM student");
		try {
			while (rs.next()) {
				System.out.printf("%-6d\t", rs.getInt("id"));
				System.out.printf("%-12s\t", rs.getString("name"));
				System.out.println(rs.getInt("age"));
			}
		} catch (Exception e) {
		}
	}
	
	/**
	 *获取数据库中最大id值
	 */
	public int getMaxId(){
		ResultSet rs = executeQuery("SELECT MAX(id) FROM student");
		
		try{
			return rs.next() ? rs.getInt(1) : 0;
		}catch(SQLException e){
			e.printStackTrace();
			return 0;
		}
	}

	public static void main(String[] args) {
		DBManager dbm = new DBManager("localhost", "mydb", "root", "bourne");
		dbm.outputDB();
		
		/*
		 * 插入一行
		 */
		int maxId = dbm.getMaxId();
		String sql = String.format("INSERT INTO student VALUES(%d, 'Jack', 23)", maxId+1);
		//因为id是自增长的,插入值为零的话则自动变为历史最大id值加1
		//String sql = "INSERT INTO student VALUES(0, 'jack', 23)";
		if(!dbm.executeUpdate(sql)){
			System.out.print("Fail to insert");
		}else{
			System.out.println("table after inserted:");
			dbm.outputDB();
		}
		
		/*
		 * 修改刚插入的行
		 */
		sql = "UPDATE student SET name='Mary' WHERE name='Jack'";
		if(!dbm.executeUpdate(sql)){
			System.out.print("Fail to update");
		}else{
			System.out.println("table after modified:");
			dbm.outputDB();
		}
		
		/*
		 * 删除刚修改的行
		 */
		sql = "DELETE FROM student WHERE name='Mary'";
		if(!dbm.executeUpdate(sql)){
			System.out.print("Fail to delete");
		}else{
			System.out.println("table after deleted:");
			dbm.outputDB();
		}
	}
}