英语渣渣学Java的day6

java练习题

1.查询员工名、部门名和所在的城市 employees,departments,locations

SELECT 
  e.first_name 员工名,
  d.department_name 部门名,
  l.city 城市
FROM
  employees e,
  departments d,
  locations l 
WHERE e.`department_id` = d.`department_id` 
  AND l.`location_id` = d.`location_id` ;

效果图：

2.利用JDBC创建下面的表格

学生表（student）
字段 名称 数据类型 约束
学号 S_NO INT(8) 主键
姓名 S_NAME VARCHAR(10)
性别 S_SEX VARCHAR(2)
入学成绩 S_SCORE DOUBLE
附加分 S_ADDF DOUBLE

package com.gec.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import java.sql.Statement;

public class DDLDemo {
	public static void main(String[] args) throws SQLException {
		//加载驱动
		 try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//获得连接
		Connection conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/shanping","root","");
		//获得语句对象
		Statement st =  conn.createStatement();
		//执行语句
		String sql = "CREATE TABLE student(S_NO INT(8) PRIMARY KEY AUTO_INCREMENT"
				+ ",S_NAME VARCHAR(10)"
				+ ",S_SEX VARCHAR(2)"
				+ ",S_SCORE DOUBLE"
				+ ",S_ADDF DOUBLE)";
		st.executeUpdate(sql);
		
		System.out.println("创建成功...");
		//释放资源
		st.close();
		conn.close();
		
	}

}

效果图：

关闭资源

使用连接工具管理连接
在工程中，通常编写一个访问数据库的工具类，此后所有访问数据库的操作，都使用工具类获取连接和关闭连接。这样做的好处是统一使用共同工具类来管理连接，简化连接操作。

繁琐的关闭资源的方法

package com.gec.close;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CloseResourceDemo {
	public static void main(String[] args) {
		//注册驱动
		try {
			//mysql com.mysql.cj.jdbc.Driver
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		//利用驱动管理获得连接 
		Connection conn =  null;
		Statement st = null;
		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/g2148", "root", "root");
			System.out.println("conn:" + conn);
			
			st = conn.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭连接 
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
}

注意：要关闭的对象越多就要用到更多的try{}；

简单的关闭资源

package com.gec.close;

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

public class JDK7CloseDemo2 {
	public static void main(String[] args) {
		//注册驱动
		try {
			//mysql com.mysql.cj.jdbc.Driver
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		//利用驱动管理获得连接 
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/g2148", "root", "root");
			Statement st = conn.createStatement();
			){
			
			System.out.println("conn:" + conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

注意：try(拥有自带关闭资源){}

ResultSet接口

执行查询SQL语句后返回的结果集，由ResultSet接口接收。

常用处理方式：遍历 / 判断是否有结果。

ResultSet对象表示数据库查询操作的结果集。访问该结果集数据需要通过光标操作。最初，光标位于第一行之前，需要调用next方法将光标移到下一行，如果在ResultSet对象上没有下一行，该方法返回false，一般可以与while循环一起使用来遍历结果集ResultSet提供了getXXX(String column)的方法来获取当前ResultSet对象当期行中指定的列的值，其中xxx是类型，与数据库列值的类型一致，column是数据库中表的列名

释放资源
数据库的连接是有限资源，相关操作结束后，养成关闭数据库的好习惯。

在finally块中依次关闭ResultSet对象、Statement对象和Connection对象

package com.gec.dql3;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SingleQuery {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");	
			Statement st = conn.createStatement();	
			ResultSet rs = st.executeQuery("select * from t_user where id = 2");
			){
			//返回结果集 遍历 
			while(rs.next()) {
				//获取列值
				/*
				 Object id = rs.getObject(1);//传的是列的索引，如果表的列过多，这将容易数错了 2）Object的数据还得强转，不方便
				 Object name = rs.getObject(2);
				 Object password = rs.getObject(3);
				 */
				int id = rs.getInt(1);
				String name = rs.getString("name");
				String password = rs.getString("password");
				System.out.println(id + ":" + name + ":" + password);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

PreparedStatement

概述
Statement主要用于执行静态SQL语句，即内容固定不变的SQL语句。Statement每执行一次都要对传入的SQL语句编译一次，效率较差。

某些情况下，SQL语句只是其中的参数有所不同，其余子句完全相同，适合使用PreparedStatement

PreparedStatement的另外一个重要好处就是预防sql注入攻击。

PreparedStatement接口
PreparedStatement接口继承自Statement接口，使用PreparedStatement时，SQL语句已提前编译，三种常用方法 execute、 executeQuery 和 executeUpdate 已被更改，以使之不再需要参数。

PreparedStatement 实例包含已事先编译的SQL语句，SQL语句可有一个或多个参数，参数的值在 SQL语句创建时未被指定。该语句为每个参数保留一个问号（“？”）作为占位符。
每个问号的值必须在该语句执行之前，通过适当的setInt或者setString 等方法提供。
由于 PreparedStatement 对象已预编译过，所以其执行速度要快于 Statement 对象。因此，多次执行的 SQL 语句经常创建为 PreparedStatement 对象，以提高效率。
PreparedStatement实现更新操作
示例,在StuDao添加更新方法，通过PreparedStatement接口实现更新emp数据

使用PreparedStatement来执行SQL语句。在SQL语句中有2个问号，在代码中要给它们分别设置值，规则是：从左到右，对应1，2，…。

通过PreparedStatement提升性能
一个sql语句执行过程中，将经历这么几个步骤：

传输SQL给数据库
数据库验证并解析SQL
计算执行计划(Access Plan)，数据库会制定出最优的访问计划。
根据访问计划进行检索，返回数据。
案例

package com.gec.preparestatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class PrepareStatementLogin2 {
	public static void main(String[] args) {
		String name = "xx";
		//String password = "123";
		
		String password = "' or '1'='1";//恒等式  1=1 2=2 3=3
		//String sql = "select * from t_user where name = '" + name + "' and password = '" + password + "'";
		
		
		String sql = "select * from t_user where name = ? and password = ?";
		
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");
			PreparedStatement st = conn.prepareStatement(sql);
			) {
			//给问号设置值
			st.setString(1, name);
			st.setString(2, password);
			
			//查询时不需要带参数
			ResultSet rs = st.executeQuery();
			//statement : sql:select * from t_user where name = 'xx' and password = '' or '1'='1'
			//prestatement:   select * from t_user where name = 'xx' and password = '\' or \'1\'=\'1'
			System.out.println("sql:" + st);
			while(rs.next()) {
				System.out.println(rs.getInt("id") + ":" + rs.getString("name") + ":" + rs.getString("password"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
			
	}
}

用jdbc做一个简单的增删改

package com.gec.preparestatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.junit.Test;

public class CUDDemo3 {
	
	@Test
	public void testAdd() {
		String sql = "insert into t_user(name,password)values(?,?)";
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");
			PreparedStatement st = conn.prepareStatement(sql);
			){
			//给问号设置值
			st.setString(1, "tom");
			st.setString(2, "222");
			
			//执行语句 
			st.executeUpdate();//不要传sql进来 
			
			System.out.println("添加成功...");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testUpdate() {
		String sql = "update t_user set name = ?,password=? where id = ?";
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");
			PreparedStatement st = conn.prepareStatement(sql);
			){
			//给问号设置值
			st.setString(1, "tom 张");
			st.setString(2, "888");
			st.setInt(3, 3);
			
			//执行语句 
			st.executeUpdate();//不要传sql进来 
			
			//打印sql
			System.out.println(st);
			
			System.out.println("修改成功...");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testDel() {
		String sql = "delete from t_user where id = ?";
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm","root", "root");
			PreparedStatement st = conn.prepareStatement(sql);
			){
			//给问号设置值
			st.setInt(1, 2);
			//执行语句 
			st.executeUpdate();// 执行ddl dml 
			
			//执行打印的sql
			System.out.println("sql:" + st);
			
			System.out.println("删除成功....");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

我们一直用class.forname() ，这句代码是为了加载驱动。其实jdbc4.0以后我们不需要再写这块代码。DriverManage已经增加自动加载驱动包的功能。当然前提是你的工程有相关的数据库驱动包。。DM会去jar包的meta-inf/services寻找文件该文件记录了驱动信息。

DAO

Data Access Object:数据访问对象
直白理解：Dao对某一个表的增删改查封装起来的一个类

程序对dao接口是感知不到操作数据库的存在，隔离开来的
Dao接口：UserDao
Void save(User user);
Void update(User user);
Void delete(int id);
User getUserById(int id);
List findUsers();
Dao接口的实现类:UserDaoImpl impl:implements
具体操作数据库的类
实现类可能是操作mysql/oracle
高内聚，低耦合（文件解耦）

案例
封装

package com.gec.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.gec.bean.User;
import com.gec.dao.UserDao;

public class UserDaoImpl implements UserDao {

	@Override
	public void save(User user) {
		String sql = "insert into t_user(name,password)values(?,?)";
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");
			PreparedStatement st = conn.prepareStatement(sql);
			){
			//给问号设置值
			st.setString(1, user.getName());
			st.setString(2, user.getPassword());
			
			//执行语句 
			st.executeUpdate();//不要传sql进来 
			
			System.out.println("执行的sql:" + st);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	@Override
	public void update(User user) {
		String sql = "update t_user set name = ?,password=? where id = ?";
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");
			PreparedStatement st = conn.prepareStatement(sql);
			){
			//给问号设置值
			st.setString(1, user.getName());
			st.setString(2, user.getPassword());
			st.setInt(3, user.getId());
			
			//执行语句 
			st.executeUpdate();//不要传sql进来 
			
			//打印sql
			System.out.println("执行的sql:" + st);
			
			System.out.println("修改成功...");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void delete(User user) {
		String sql = "delete from t_user where id = ?";
		try(
			Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm","root", "root");
			PreparedStatement st = conn.prepareStatement(sql);
			){
			//给问号设置值
			st.setInt(1, user.getId());
			//执行语句 
			st.executeUpdate();// 执行ddl dml 
			
			//执行打印的sql
			System.out.println("sql:" + st);
			
			System.out.println("删除成功....");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public User getUserById(int id) {
		String sql = "select * from t_user where id = ?";
		try(
				Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");	
				PreparedStatement st = conn.prepareStatement(sql);
				
				){
				st.setInt(1, id);
				ResultSet rs = st.executeQuery();
				System.out.println("执行的sql:" + st);
				//返回结果集 遍历 
				while(rs.next()) {
					//创建用户对象
					User user = new User();
					int uid = rs.getInt(1);
					String name = rs.getString("name");
					String password = rs.getString("password");
					
					user.setId(uid);
					user.setName(name);
					user.setPassword(password);
					
					//返回用户信息
					return user;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		return null;
	}

	@Override
	public List<User> findUsers() {
		//定义集合
		List<User> users = new ArrayList<>();
		try(
				Connection conn = DriverManager.getConnection("jdbc:mysql:///hrm", "root", "root");	
				PreparedStatement st = conn.prepareStatement("select * from t_user");	
				ResultSet rs = st.executeQuery();
				){
				//返回结果集 遍历 
				while(rs.next()) {
					//创建用户对象
					User user = new User();
					int uid = rs.getInt(1);
					String name = rs.getString("name");
					String password = rs.getString("password");
					
					user.setId(uid);
					user.setName(name);
					user.setPassword(password);
					//每个用户放到集合里面
					users.add(user);
				}
				
				return users;
			} catch (Exception e) {
				e.printStackTrace();
			}
		return null;
	}

}

调用

package com.gec.dao;

import java.util.List;

import com.gec.bean.User;

/*
 * 增删改查方法
 * dao:data access object
 */
public interface UserDao {
	
	/** alt+shift+j 快速生成注释
	 * @param user
	 */
	void save(User user);
	
	/**修改用户信息
	 * @param user
	 */
	void update(User user);
	
	/**删除用户信息
	 * @param user
	 */
	void delete(User user);
	
	/**根据id查找用户
	 * @param id
	 * @return
	 */
	User getUserById(int id);
	
	/**查找所有用户
	 * @return
	 */
	List<User> findUsers();
}

package com.gec.bean;

public class User {
	private int id;
	private String name;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
	}
	public User(int id, String name, String password) {
		super();
		this.id = id;
		this.name = name;
		this.password = password;
	}
	public User() {
		super();
	}
	
}

优化封装我就不写了，我自己都看不懂
参考：
https://blog.csdn.net/qq_36962829/article/details/79035067