首頁  >  文章  >  資料庫  >  Jdbc--具體程式碼實現

Jdbc--具體程式碼實現

黄舟
黄舟原創
2017-02-11 11:10:391443瀏覽

1、獲取連接

1) 把jar包複製到項目目錄下
2) 把jar包加入項目的buildpath環境變量中, 使得classpath也和buildpath一致, 讓類加載器便於加載具體子類
3) 反射式載入驅動程式類別, 會自動註冊驅動程式

4) 透過驅動程式管理器取得連線.

       在目前工程下建立jdbc.properties,其中內容如下(是連接.資料庫所需的四大參數分別是:載入驅動器、取得連接資料庫的IP及連接埠、使用者名,密碼),目的是方便呼叫和修改!

#driverClass = oracle.jdbc.driver.OracleDriver
#url = jdbc:oracle:thin:@127.0.0.1:1521:orcl
#user = scott#password = tigerdriverClass = com.mysql.jdbc.Driverurl = jdbc:mysql://127.0.0.1:3306/companyuser = root
password = 123456

獲取連接以及關閉連接的具體實現如下:


package com.atguigu.jdbc;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.junit.Test;
	
 /**
 * 获取连接
 * 1) 把jar包复制到项目目录下
 * 2) 把jar包加入项目的buildpath环境变量中, 使得classpath也和buildpath一致, 让类加载器便于加载具体子类
 * 3) 反射加载驱动程序类, 会自动注册驱动程序
 * 4) 通过驱动程序管理器获取连接.
 * @author Administrator
 *
 */
public class DriverTest {
// 使用Properties类对象的getPropety方法与FileInputStream方法获取文件中的内容,从而创建Connection对象
	@Test
	public void test5() throws Exception {
		Properties properties = new Properties();
		properties.load(new FileInputStream("jdbc.properties"));
		String driverClass = properties.getProperty("driverClass");
		String url = properties.getProperty("url");
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		Class.forName(driverClass); // 只需要加载类, 类的静态语句块就会被执行, 创建驱动程序对象,并把此对象注册到驱动程序管理器中.
		Connection connection = DriverManager.getConnection(url, user, password);
		System.out.println(connection);
		connection.close();
	}
}

package com.atguigu.jdbc;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {
	// 获取建立连接对象
	public static Connection getConnection() throws IOException, 
													ClassNotFoundException, 
													SQLException {
		Properties properties = new Properties();
		properties.load(new FileInputStream("jdbc.properties"));
		String driverClass = properties.getProperty("driverClass");
		String url = properties.getProperty("url");
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		Class.forName(driverClass); // 只需要加载类, 类的静态语句块就会被执行, 创建驱动程序对象,并把此对象注册到驱动程序管理器中.
		Connection connection = DriverManager.getConnection(url, user, password);
		return connection;
	}

	// 关闭资源
	public static void close(Connection connection) {
		close(connection, null);
	}
	
	public static void close(Connection connection, Statement statement) {
		close(connection, statement, null);
	}
	
	public static void close(Connection connection, Statement statement, ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}


package com.atguigu.jdbc;

public class User {

	private String user;
	private String password;

	public User() {
	}

	public User(String user, String password) {
		super();
		this.user = user;
		this.password = password;
	}

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Override
	public String toString() {
		return "User [user=" + user + ", password=" + password + "]";
	}

}

package com.atguigu.jdbc;

import java.sql.Connection;
import java.sql.Statement;

import org.junit.Test;

// 使用Statement执行创建user表,并插入三行数据
public class StatementTest {
	
	@Test
	public void test1() {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = JdbcUtil.getConnection();
			statement = connection.createStatement();// 获取执行体对象
			// 执行SQL
			// 创建user表
			String sql = "create table if not exists user(user varchar(50), password varchar(100))";
			int rows = statement.executeUpdate(sql); // 执行的DDL语句, 还可以执行DML
			System.out.println(rows + " rows affected..");
			rows = statement.executeUpdate("insert into user values('admin','admin')");
			System.out.println(rows + " rows affected..");
			rows = statement.executeUpdate("insert into user values('user1','user1')");
			System.out.println(rows + " rows affected..");
			rows = statement.executeUpdate("insert into user values('user2','123456')");
			System.out.println(rows + " rows affected..");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, statement);
		}
	}
}
作為固定方法放在JdbcUtil類別中,方便使用:

package com.atguigu.jdbc;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

import org.junit.Test;

import java.sql.Statement;

public class TestStatement {

	// 弊端:需要拼写sql语句,并且存在SQL注入的问题
	@Test
	public void testLogin() {
		Scanner scan = new Scanner(System.in);
		System.out.print("用户名:");
		String userName = scan.nextLine();
		System.out.print("密   码:");
		String password = scan.nextLine();

		String sql = "select user, password from user where user = '"
				+ userName + "' and password = '" + password + "'"; 
		
		System.out.println(sql);
		
		User user = get(sql, User.class);
		if(user != null){
			System.out.println("登陆成功!");
		}else{
			System.out.println("用户名或密码错误!");
		}
	}

	public <T> T get(String sql, Class<T> clazz) {// (sql, Customer.class)
		T t = null;

		Connection conn = null;
		Statement stam = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();

			stam = conn.createStatement();

			rs = stam.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); // 注意:反射根据Java中类的属性获取Field对象
					field.setAccessible(true);
					field.set(t, columnVal);
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		//JDBCUtils.close(rs, stam, conn);
		}

		return t;
	}

}

Statement類

package com.atguigu.jdbc;

public class Customer {

	private String name;
	private String gender;
	private String phone;

	public Customer(String name, int age, String gender, String phone,) {
		super();
		this.name = name;
		this.age = age;
		this.gender = gender;
		this.phone = phone;
	}

	public Customer() {
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	@Override
	public String toString() {
		return "Customer [name=" + name + ", gender=" + gender + ", phone=" + phone + "]";
	}

}

Statement類

到Statement類,首先我們需要建立一個User類別以與Statement類別中建立的在company資料庫中的的user表相對應:

public class PreparedStatementTest {	
	@Test
	public void exer1() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JdbcUtil.getConnection();
			String sql = "create table if not exists customer(name varchar(30), gender enum(&#39;男&#39;,&#39;女&#39;) default &#39;男&#39;, phone varchar(20))";
			preparedStatement = connection.prepareStatement(sql);
			 
			preparedStatement.executeUpdate();
			
			JdbcUtil.close(null, preparedStatement); // 在获取新的预编译对象前,一定要先关闭原来的.
			
			String sql2 = "insert into customer(name, gender, phone) values(?, ?, ?)";
			preparedStatement = connection.prepareStatement(sql2); // 要想重新执行新的SQL,必须再次预编译
			
			preparedStatement.setString(1, "张三");
			preparedStatement.setString(2, "男");
			preparedStatement.setString(3, "13343493434");
			
			int rows = preparedStatement.executeUpdate();
			System.out.println(rows + " rows affected.");
			
			preparedStatement.setString(1, "李四");
			preparedStatement.setString(2, "女");
			preparedStatement.setString(3, "1322243434");
			
			rows = preparedStatement.executeUpdate();
			System.out.println(rows + " rows affected.");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, preparedStatement);
		}
		// 并通过客户端验证.
	}
}

使用Statement執行建立user表,並插入三行資料:

public class PreparedStatementTest {

// 在Customer类中添加属性int age,double weight,给Customer表中添加对应的列,并添加记录
	@Test
	public void test3() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JdbcUtil.getConnection();
			String sql = "alter table customer add age int after name";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.executeUpdate();
			JdbcUtil.close(null, preparedStatement);
			sql = "alter table customer add weight double";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.executeUpdate();
			JdbcUtil.close(null, preparedStatement);
			
			sql = "insert into customer(name, age, gender, phone, weight) values (?, ?, ?, ?, ?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "王五");
			preparedStatement.setInt(2, 50);
			preparedStatement.setString(3, "男");
			preparedStatement.setString(4, "134234234234");
			preparedStatement.setDouble(5, 98.5);
			
			int rows = preparedStatement.executeUpdate();
			System.out.println(rows + " rows affected");
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, preparedStatement);
		}
	}

	// 添加记录
	@Test
	public void test4() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JdbcUtil.getConnection();
			String sql = "alter table customer add birthdate date";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.executeUpdate();
			JdbcUtil.close(null, preparedStatement);
			
			sql = "insert into customer(name, age, gender, phone, weight, birthdate) values (?, ?, ?, ?, ?, ?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "赵六");
			preparedStatement.setInt(2, 60);
			preparedStatement.setString(3, "女");
			preparedStatement.setString(4, "13882342323");
			preparedStatement.setDouble(5, 40);
			preparedStatement.setString(6, "1960-2-3");
			
			int rows = preparedStatement.executeUpdate();
			System.out.println(rows + " rows affected");
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, preparedStatement);
		}
	}

	// 再添加记录
	@Test
	public void test5() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JdbcUtil.getConnection();
			String sql = "insert into customer(name, age, gender, phone, weight, birthdate) values (?, ?, ?, ?, ?, ?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setObject(1, "张七");
			preparedStatement.setObject(2, 20);
			preparedStatement.setObject(3, "女");
			preparedStatement.setObject(4, "1343434343");
			preparedStatement.setObject(5, 58.8);
			preparedStatement.setObject(6, "1980-3-8");
			
			int rows = preparedStatement.executeUpdate();
			if (rows == 1) {
				System.out.println("插入成功");
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, preparedStatement);
		}
	}
}
然而Statement存在
弊端:不僅需要拼字語句,並且存在SQL注入的問題,具體問題在下面的小例子中體現我們需要使用此程式
正確的用戶名,和對應的密碼才能進入登陸成功,透過上面的程式我們可知用戶名和密碼分別是:

+-------+---------- +| user  | password |

+-------+----------+

| admin | admin    || admin | admin    |

易| user1 | 1
|
| user2 | 123456   |

+-------+----------+

正常情況下我們登陸情況如下圖:

但是SQL高手透過以下操作就能登陸成功,這就是SQL注入的問題



使用他的子類別PreparedStatement預先編譯執行體,下面我們進行PreparedStatement的練習:

先建立Customer類別

// 寫一個類別Customer, ph

package com.atguigu.jdbc;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class CommonUtil {
	
	/**
	 * 通用更新操作
	 * @param sql 一个可以包含?的SQL语句
	 * @param values SQL中有多少个?,可变参数就有多少个具体值
	 * @return 更新以后影响的记录数
	 */
	public static int commonUpdate(String sql, Object... values) throws  FileNotFoundException, 													
	ClassNotFoundException,												IOException, 
											SQLException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JdbcUtil.getConnection(); // 获取连接
			preparedStatement = connection.prepareStatement(sql); // 把带有?的SQL编译
			for (int i = 0; i < values.length; i++) { // 可变参数的长度就是?的个数
				preparedStatement.setObject(i + 1, values[i]); // 统一给所有的?替换成具体的值
			}
			return preparedStatement.executeUpdate(); // 替换完?或没有?, 执行更新操作
		} finally {
			JdbcUtil.close(connection, preparedStatement); // 关闭资源
		}
	}
}

在測試類別中使用PreparedStatment執行體物件建立對應的表, 並插入2條資料。

package com.atguigu.jdbc;

import java.util.List;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import org.junit.Test;

public class ResultSetTest {
	@Test
	public void test1() {
		// select * from customer; 结果如下
		/*
		+------+------+--------+------------+--------+------------+
		| name | age  | gender | phone      | weight | birthdate  |
		+------+------+--------+------------+--------+------------+
		| 张七       20 | 女         | 1343434343 |   58.8 | 1980-03-08 |
		| 张九   |    40 | 男        | 1349932423 |   78.2 | 1977-08-08 |
		| 张三    |    3  | 女       | 1332324234 |     70 | 1979-08-08 |
		+------+------+--------+------------+--------+------------+
		*/
		Connection connection = null;
		PreparedStatement prepareStatment = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtil.getConnection();
			String sql = "select * from customer";
			prepareStatment = connection.prepareStatement(sql);
			// 调用PreparedStatement对象的executeQuery方法产生ResultSet对象
			resultSet = prepareStatment.executeQuery();
			System.out.println(resultSet);
			while (resultSet.next()) { // 根据列的<span style="color:#ff0000;">序号</span>,
			先检测当前游标的下一行是否有数据, 如果有数据则移动游标,并返回true, 如果没有数据,直接返回false
				String name = resultSet.getString(1); // 获取到的是当前游标指向的行的第1列的值
				int age = resultSet.getInt(2);// 获取到的是当前游标指向的行的第2列的值
				String gender = resultSet.getString(3);// 获取到的是当前游标指向的行的第3列的值
				String phone = resultSet.getString(4);// 获取到的是当前游标指向的行的第4列的值
				double weight = resultSet.getDouble(5);// 获取到的是当前游标指向的行的第5列的值
				Date birthDate = resultSet.getDate(6);// 获取到的是当前游标指向的行的第6列的值
				System.out.println(name + "\t" + age + "\t" + gender + "\t" + phone + "\t" + weight + "\t" + birthDate);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, prepareStatment, resultSet);
		}
	}

	// 我们改变查询列名的顺序进行查询操作,使用下标不能实现了,我们需要根据虚表中的<span style="color:#ff0000;">列名</span>
	进行查询,并将查询结果封装成对象,添加到list集合中
	@Test
	public void test2() {
		Connection connection = null;
		PreparedStatement prepareStatment = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtil.getConnection();
			// name | age  | gender | phone      | weight | birthdate  |
			String sql = "select name custName, gender, age, weight, phone, birthdate birth from customer";
			prepareStatment = connection.prepareStatement(sql);
			resultSet = prepareStatment.executeQuery();
			List<Customer> list = new ArrayList<Customer>();
			// 基于虚表的结果集
			while (resultSet.next()) {
				String name = resultSet.getString("custName");// 获取到的是当前游标指向的行的虚表中名字为name列的值
				int age = resultSet.getInt("age");// 获取到的是当前游标指向的行的虚表中名字为age列的值
				String gender = resultSet.getString("gender");// 获取到的是当前游标指向的行的虚表中名字为gender列的值
				String phone = resultSet.getString("phone");
				double weight = resultSet.getDouble("weight");
				Date birthDate = resultSet.getDate("birth");
				Customer customer = new Customer(name, age, gender, phone, weight, birthDate); // 封装成对象 O/R mapping
				list.add(customer);
			}
			
			// 遍历集合
			for (Customer cust : list) {
				System.out.println(cust);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, prepareStatment, resultSet);
		}
	}
	// 我们改变查询列名的顺序进行查询年龄大于任意数的操作,使用下标不能实现了,我们需要根据<span style="color:#ff0000;">虚表中的列名</span>
	进行查询,并将查询结果封装成对象,添加到list集合中
	@Test
	public void test3() {
		Connection connection = null;
		PreparedStatement prepareStatment = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtil.getConnection();
			// name | age  | gender | phone      | weight | birthdate  |
			String sql = "select name custName, gender, age, weight, phone, birthdate birth from customer where age > ?";
			prepareStatment = connection.prepareStatement(sql);
			
			prepareStatment.setObject(1, 20);
			
			resultSet = prepareStatment.executeQuery();
			List<Customer> list = new ArrayList<Customer>();
			// <span style="color:#ff0000;">基于虚表的结果集</span>
			while (resultSet.next()) {
				String name = resultSet.getString("custName");// 获取到的是当前游标指向的行的
				<span style="color:#ff0000;">虚表</span>中名字为name列的值
				int age = resultSet.getInt("age");// 获取到的是当前游标指向的行的虚表中名字为age列的值
				String gender = resultSet.getString("gender");// 获取到的是当前游标指向的行的虚表中名字为gender列的值
				String phone = resultSet.getString("phone");
				double weight = resultSet.getDouble("weight");
				Date birthDate = resultSet.getDate("birth");
				Customer customer = new Customer(name, age, gender, phone, weight, birthDate); // 封装成对象 O/R mapping
				list.add(customer);
			}
			
			// 遍历集合
			for (Customer cust : list) {
				System.out.println(cust);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, prepareStatment, resultSet);
		}
	}

	//  获取查询结果,若查询结果并没有类与其对应,就需要获取虚表的表结构进行查询操作的打印输出
	@Test
	public void test4() {
		Connection connection = null;
		PreparedStatement prepareStatment = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtil.getConnection();
			// name | age  | gender | phone      | weight | birthdate  |
			String sql = "select name custName, gender, age, weight, phone, birthdate birth from customer";
			prepareStatment = connection.prepareStatement(sql);
			resultSet = prepareStatment.executeQuery();
			/*
			+----------+--------+------+--------+------------+------------+
			| custName | gender | age  | weight | phone      | birth      |
			+----------+--------+------+--------+------------+------------+
			| 张七     		| 女           |   20 |   58.8 | 1343434343 | 1980-03-08 |
			| 张九     		| 男          |   40 |   78.2 | 1349932423 | 1977-08-08 |
			| 张三   	    | 女          |   30  |     70 | 1332324234 | 1979-08-08 |
			+----------+--------+------+--------+------------+------------+
			*/
			ResultSetMetaData metaData = resultSet.getMetaData(); // 获取虚表的表结构对象
			int cols = metaData.getColumnCount(); // 获取虚表共有多少列
			//System.out.println(cols);
			
			String colName = metaData.getColumnName(3);// 获取基表的列名, 参数中的是第几列
			//System.out.println(colName);
			
			colName = metaData.getColumnName(6);// 获取基表列名, 参数中的是第几列
			//System.out.println(colName);
			
			String colLabel = metaData.getColumnLabel(6); // 获取虚表列名, 参数是第6列
			//System.out.println(colLabel);
			
			System.out.println("---------------------------------");
			
			for (int i = 0; i < cols; i++) {
				String columnLabel = metaData.getColumnLabel(i + 1); // 列的索引总是从1开始 
				System.out.print(columnLabel + "\t");
			}
			System.out.println();
			
			while (resultSet.next()) {
				for (int i = 0; i < cols; i++) {
					String columnLabel = metaData.getColumnLabel(i + 1); // 先获取列的别名
					Object value = resultSet.getObject(columnLabel); // 根据列的别名再获取对应的值
					System.out.print(value + "\t");
				}
				System.out.println();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(connection, prepareStatment, resultSet);
		}
	}	
}

在Customer類別中新增屬性int age,double weight,在Customer表中新增對應的列,並新增記錄

rrreee

像上面那樣添加每次重複同樣的程式碼,我們

rrreee像上面那樣添加每個這裡重複相同的程式碼,我們都在這裡設定的通用程式碼更新操作,並存入CommonUtil類別中,作為工具類別通用更新操作。具體實現如下:

rrreee

ResultSet類


至此我們實現了資料庫資料的建表,插入,更新,刪除,下面進行資料庫操作中比較難的結果,需要使用到困難的查詢集ResultSet類,循序漸進的練習實作如下:


rrreee

rrreee
Jg

(Pwwwg--o--Pwww. php.cn)!




🎜🎜🎜🎜🎜🎜🎜🎜









陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn