JDBC 速查手册

jdbc:mysql://主机名称:mysql服务端口号(默认为3306)/数据库名称?参数1=值1&参数2=值2

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8

jdbc:mysql://localhost:3306/test?user=root&password=123456

@Testpublic void testConnection1() throws Exception {    // 获取 Driver 实现类对象    Driver driver = new com.mysql.jdbc.Driver();
    // test：test数据库    String url = "jdbc:mysql://localhost:3306/test";    // 将用户名和密码封装在 Properties 中    Properties info = new Properties();    info.setProperty("user", "root");    info.setProperty("password", "abc123");
    Connection conn = driver.connect(url, info);  	System.out.println(conn);}

@Testpublic void testConnection2() throws Exception {    // 1. 获取 Driver 实现类对象：使用反射    Class clazz = Class.forName("com.mysql.jdbc.Driver");    Driver driver = (Driver) clazz.newInstance();
    // 2. 提供要连接的数据库    String url = "jdbc:mysql://localhost:3306/test";
    // 3. 提供连接需要的用户名和密码    Properties info = new Properties();    info.setProperty("user", "root");    info.setProperty("password", "abc123");
    // 4. 获取连接    Connection conn = driver.connect(url, info);    System.out.println(conn);}

@Testpublic void testConnection3() throws Exception {    // 1. 获取 Driver 实现类的对象    Class clazz = Class.forName("com.mysql.jdbc.Driver");    Driver driver = (Driver) clazz.newInstance();
    // 2. 提供另外三个连接的基本信息：    String url = "jdbc:mysql://localhost:3306/test";    String user = "root";    String password = "abc123";
    // 注册驱动    DriverManager.registerDriver(driver);
    // 获取连接    Connection conn = DriverManager.getConnection(url, user, password);    System.out.println(conn);}

@Testpublic void testConnection4() throws Exception {    // 1. 提供三个连接的基本信息：    String url = "jdbc:mysql://localhost:3306/test";    String user = "root";    String password = "abc123";        // 2. 加载 Driver    Class.forName("com.mysql.jdbc.Driver");
    // 3.获取连接    Connection conn = DriverManager.getConnection(url, user, password);    System.out.println(conn);}

static {    try {        java.sql.DriverManager.registerDriver(new Driver());    } catch (SQLException E) {        throw new RuntimeException("Can't register driver!");    }}

public class ConnectionTest {  @Test  public void getConnection5() throws Exception{   		// 1. 读取配置文件中的 4 个基本信息    	InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
      Properties pros = new Properties();      pros.load(is);
      String user = pros.getProperty("user");      String password = pros.getProperty("password");      String url = pros.getProperty("url");      String driverClass = pros.getProperty("driverClass");
      // 2. 加载驱动      Class.forName(driverClass);
      // 3. 获取连接      Connection conn = DriverManager.getConnection(url, user, password);      System.out.println(conn);  }}

user=rootpassword=abc123url=jdbc:mysql://localhost:3306/testdriverClass=com.mysql.jdbc.Driver

public class StatementTest {	@Test	public void testLogin() {		Scanner scan = new Scanner(System.in);
		System.out.print("用户名：");		String userName = scan.nextLine(); // 这里之所以用 nextLine()，而不是 next() 方法，是因为要从标准输入中读取："1' OR "和"=1 OR '1' = '1'"		System.out.print("密码：");		String password = scan.nextLine();
		// SELECT user, password FROM user_table WHERE USER = '1' OR ' AND PASSWORD = '=1 OR '1' = '1';		String sql = "SELECT user, password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password				+ "'";		User user = get(sql, User.class);		if (user != null) {			System.out.println("登陆成功!");		} else {			System.out.println("用户名或密码错误！");		}	}
	// 使用Statement实现对数据表的查询操作	public <T> T get(String sql, Class<T> clazz) {		T t = null;		Connection conn = null;		Statement st = null;		ResultSet rs = null;    		try {			// 1. 加载配置文件      InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");			Properties pros = new Properties();			pros.load(is);
			// 2. 读取配置信息			String user = pros.getProperty("user");			String password = pros.getProperty("password");			String url = pros.getProperty("url");			String driverClass = pros.getProperty("driverClass");
			// 3. 加载驱动			Class.forName(driverClass);
			// 4. 获取连接			conn = DriverManager.getConnection(url, user, password);
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			// 获取结果集的元数据			ResultSetMetaData rsmd = rs.getMetaData();
			// 获取结果集的列数			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {					// //1. 获取列的名称					// String columnName = rsmd.getColumnName(i+1);
					// 1. 获取列的别名					String columnName = rsmd.getColumnLabel(i + 1);
					// 2. 根据列名获取对应数据表中的数据					Object columnVal = rs.getObject(columnName);
					// 3. 将数据表中得到的数据，封装进对象					Field field = clazz.getDeclaredField(columnName);					field.setAccessible(true);					field.set(t, columnVal);				}				return t;			}		} catch (Exception e) {			e.printStackTrace();		} finally {			// 关闭资源			if (rs != null) {				try {					rs.close();				} catch (SQLException e) {					e.printStackTrace();				}			}			if (st != null) {				try {					st.close();				} catch (SQLException e) {					e.printStackTrace();				}			}
			if (conn != null) {				try {					conn.close();				} catch (SQLException e) {					e.printStackTrace();				}			}		}
		return null;	}}