jdbc入门

JDBC（Java DataBase Connectivity）：是SUN制定的一套java连接/操作数据库的规范。属于Java SE的一部分，接口和类主要分布在java.sql和javax.sql中。




Java应用程序只是简单的调用JDBC API完成数据库的操作，底层的代码是由具体的Driver（各个数据库生产厂商提供）实现。


我的第一个JDBC程序

		// First, register jdbc driver
		Class.forName("com.mysql.jdbc.Driver");

		// Second, get connection
		Connection conn = DriverManager
				.getConnection("jdbc:mysql://localhost/jdbc?user=root&password=root");

		// Third, create sql statement
		Statement stmt = conn.createStatement();

		// Fourth, execute statement
		ResultSet rs = stmt.executeQuery("select * from t_user");

		// Last, iterate result set
		while (rs.next()) {
			System.out.println(rs.getString("name") + ", " + rs.getInt("age"));
		}

两种常用的注册驱动的方法

		// register mysql jdbc driver
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());

		// Class.forName("com.mysql.jdbc.Driver");

调用DriverManager.registerDriver(new Driver())会导致添加两遍Driver实例

URL格式
JDBC:子协议:子名称//主机名:端口/数据库名?属性名=属性值&...

使用Satement时，如果操作带一些条件，需要拼接SQL，会导致SQL注入问题。

String name = "' or 1 or '";
String sql = "select * from t_user where name = '" + name + "'";

/*
	 
  一个新的 Statement 对象，该对象将生成具有给定类型、并发性和可保存性的 ResultSet 对象
1、结果集类型：设置是否滚动，以及设置滚动结果集是否感知数据的更新
	ResultSet.TYPE_FORWARD_ONLY 只能向前
	ResultSet.TYPE_SCROLL_INSENSITIVE 可滚动，不感知数据变化。
	ResultSet.TYPE_SCROLL_SENSITIVE 可滚动，并感知数据变化。
2、并发性：设置是否允许更新
	ResultSet.CONCUR_READ_ONLY 只读
	ResultSet.CONCUR_UPDATABLE 可更新
3、可保存性：设置提交时候是否关闭结果集
	ResultSet.HOLD_CURSORS_OVER_COMMIT 
	在提交后结果集还可用ResultSet.CLOSE_CURSORS_AT_COMMIT:在提交时候关闭结果集

由于这些特性比较高级，不同数据库驱动对此实现也不一样。
因此在使用JDBC高级特性的时候最好做个测试，以保证程序的可靠性
	  
	 */

获取自动生成主键的值

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();

			String sql = "insert into t_user(name, age, salary, birthday) values (?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, "小时代" + Math.random());
			pstmt.setInt(2, 12);
			pstmt.setFloat(3, 5000);
			pstmt.setDate(4, new java.sql.Date(System.currentTimeMillis()));

			pstmt.executeUpdate();

			rs = pstmt.getGeneratedKeys();
			if (rs.next())
				System.out.println(rs.getObject(1));
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException("Execute failed!", e);
		} finally {
			JdbcUtils.closeResource(conn, pstmt, rs);
		}

批量添加多条记录

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into t_user(name,birthday,age,salary) values(?,?,?,?)";
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < 100; i++) {
				pstmt.setString(1, "Batch Name" + i);
				pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
				pstmt.setInt(3, 23 + i);
				pstmt.setFloat(4, 5000 + i);

				pstmt.addBatch();
			}
			pstmt.executeBatch();

			rs = pstmt.getGeneratedKeys();

			while (rs.next()) {
				System.out.println(rs.getInt("GENERATED_KEY"));
				// System.out.println(rs.getInt(1));
			}
		} catch (SQLException e) {
			throw new DAOException("Failed to execute batch sqls.", e);
		} finally {
			JdbcUtils.closeResource(conn, pstmt, rs);
		}

模拟一个简单的数据源

public class MyDataSource implements DataSource {
	private static String url = "jdbc:mysql://localhost:3306/jdbc";
	private static String username = "root";
	private static String password = "root";

	private LinkedList<Connection> pool = new LinkedList<Connection>();

	private static int initialSize = 3;
	private static int maxSize = 5;
	private static int maxUsedCount = 5;

	private int currentSize = 0;

	public MyDataSource() {
		try {
			for (int i = 0; i < initialSize; i++) {
				pool.addLast(createConnection());
				currentSize++;
			}
		} catch (SQLException e) {
			throw new ExceptionInInitializerError(
					"DataSource initialization failed!");
		}
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {

	}

	@Override
	public void setLoginTimeout(int seconds) throws SQLException {

	}

	@Override
	public int getLoginTimeout() throws SQLException {
		return 0;
	}

	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return false;
	}

	@Override
	public Connection getConnection() throws SQLException {
		synchronized (pool) {
			if (pool.size() > 0) {
				return pool.removeFirst();
			}

			if (currentSize < maxSize) {
				Connection conn = createConnection();
				currentSize++;
				return conn;
			}

			throw new SQLException("Out of max size");
		}
	}

	private Connection createConnection() throws SQLException {
		Connection realConn = DriverManager.getConnection(url, username,
				password);
		return new MyProxyConnection(realConn).getConnection();
	}

	@Override
	public Connection getConnection(String username, String password)
			throws SQLException {
		throw new SQLException("Not implemented.");
	}

	private class MyProxyConnection implements InvocationHandler {
		private Connection real;
		private Connection wrappedConn;
		private int currentUsedCount = 0;

		public MyProxyConnection(Connection realConnection) {
			this.real = realConnection;
		}

		public Connection getConnection() {
			wrappedConn = (Connection) Proxy.newProxyInstance(
					MyProxyConnection.class.getClassLoader(),
					new Class[] { Connection.class }, this);
			return wrappedConn;
		}

		@Override
		public Object invoke(Object proxy, Method method, Object[] args)
				throws Throwable {
			if ("close".equals(method.getName())) {
				currentUsedCount++;
				if (currentUsedCount < MyDataSource.maxUsedCount)
					MyDataSource.this.pool.addLast(wrappedConn);
				else {
					real.close();
					MyDataSource.this.currentSize--;
				}
				return null;
			} else if ("toString".equals(method.getName())) {
				return "My_Proxy_Connection@"
						+ Integer.toHexString(real.hashCode());
			}
			return method.invoke(real, args);
		}
	}
}

Spring JDBC Template使用

public class SimpleDaoImpl extends SimpleJdbcDaoSupport {
	private static final String ADD_USER = "insert into t_user(name, age, birthday,salary) values(:name, :age, :birthday, :salary)";
	private static DataSource dataSource;

	static {
		String url = "jdbc:mysql://localhost:3306/jdbc";
		String username = "root";
		String password = "root";
		DriverManagerDataSource ds = new DriverManagerDataSource(url, username,
				password);
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource = ds;
	}

	public static void main(String[] args) {
		SimpleDaoImpl dao = new SimpleDaoImpl();
		dao.setDataSource(dataSource);

		User user = new User();
		user.setName("Test Simple JDBC Support");
		user.setAge(23);
		user.setBirthday(new Date());
		user.setSalary(8967);

		dao.addUser(user);
	}

	public void addUser(User user) {
		int status = getSimpleJdbcTemplate().update(ADD_USER,
				new BeanPropertySqlParameterSource(user));

		System.out.println("Affect Rows: " + status);
	}
}

>>更多Spring JDBC Template
http://my.oschina.net/u/218421/blog/38598