jdbctest.java

《基于Eclipse的开源框架技术与实战》[第6章]随书源码

package jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
 * <p>Title: Eclipse Plugin Development</p>
 * <p>Description: Free download</p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: Free</p>
 * @author gan.shu.man
 * @version 1.0
 */

/**
 * 通过SQL语句对数据库增、删、改、查 
 */
public class JdbcTest {

	/**
	 * 创建数据库表的SQL语句
	 * */
	private static final String EMPLOYEE_TABLE = "create table MyEmployees( "
			+ "   id INT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20), "
			+ "   title VARCHAR(20), salary INT )";

	/**
	 * 创建数据库连接的Connection对象
	 * */
	public static Connection getConnection() throws Exception {
		//驱动程序
		String driver = "org.gjt.mm.mysql.Driver";
		//数据库地址
		String url = "jdbc:mysql://localhost/test";
		String username = "root";
		String password = "";
		//加载驱动器
		Class.forName(driver);
		//建立连接
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}
	
	/**
	 * 遍历结果集，并打印
	 * @param rs 结果集
	 * */
	public static void printData(ResultSet rs)throws Exception{
         //处理结果集
		while(rs.next()){
			String firstName = rs.getString(2);
			String lastName = rs.getString(3);
			
			System.out.println("name = "+firstName+lastName);
		}
	}

	public static void main(String args[]) {
		Connection conn = null;
		Statement stmt = null;
		try {
			//得到数据库连接
			conn = getConnection();
			//创建Statement对象
			stmt = conn.createStatement();
			//通过Statement创建数据库表
			System.out.println("Create table-----------");
			stmt.executeUpdate(EMPLOYEE_TABLE);
			//为表MyEmployees3插入2条纪录
			System.out.println("Insert table-----------");
			stmt.executeUpdate("insert into MyEmployees(id, firstName, lastName) values(100, 'gan', '.shuman')");
			stmt.executeUpdate("insert into MyEmployees(id, firstName, lastName) values(200, 'wan', '.xiumin')");
			ResultSet rs =  stmt.executeQuery("select * from MyEmployees");
			//打印结果
			printData(rs);
			System.out.println("Update table-----------");
			//更新数据库，把firstName改为'zhang'
			stmt.executeUpdate("update MyEmployees set firstName = 'zhang'");
			rs =  stmt.executeQuery("select * from MyEmployees");
			//打印结果
			printData(rs);
			System.out.println("Drop table-----------");
			//删除表
			stmt.executeUpdate("drop table myemployees");
		} catch (ClassNotFoundException e) {
			System.out.println("error: failed to load MySQL driver.");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("error: failed to create a connection object.");
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("other error:");
			e.printStackTrace();
		} finally {
			try {
				//释放Statement和Connection
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}