Java个人学习之旅(第二十四天)

数据库：

1. 数据库的内置函数

1. sum()
2. count()
3. avg()
4. length(str)
    - 返回字符类型的字节数

2. 多表查询以及相关的查询语句

1. SQL99
   支持内连接 inner
   左连接 left
   右连接 right
   

2. SQL92
   该方式只适合内连接,不建议使用
   

3. group by : 分组查询

4. order by :

• desc 降序排列
• asc 升序排列(默认)

5. distinct : 去除重复

6. having : 允许语句在分组查询后再次用聚合函数筛选

3. JDBC连接模板

public class JDBCUtil {

	private static String driverClass;
	private static String url;
	private static String username;
	private static String password;

	static {

		ResourceBundle bundle = ResourceBundle.getBundle("jdbc");

		driverClass = bundle.getString("driverClass");
		url = bundle.getString("url");
		username = bundle.getString("username");
		password = bundle.getString("password");
	}

	/**
	 * 注册驱动
	 * 
	 * @throws ClassNotFoundException
	 */
	public static void loadDriver() throws ClassNotFoundException {
		Class.forName(driverClass);
	}

	/**
	 * 获得连接
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Connection getConnection() throws SQLException, ClassNotFoundException {
		loadDriver();
		Connection conn = DriverManager.getConnection(url,username,password);

		return conn;
	}

	/*
	 * 释放资源
	 */
	public static void realese(Statement stmt, Connection conn) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}

	/*
	 * 释放资源
	 */
	public static void release(ResultSet rs, Statement stmt, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			rs = null;
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn = null;
		}
	}
}

参考文章:关于sql92和sql99