JDBC快速入门
/*
JDBC快速入门
步骤:
1.导入驱动jar包
2.注册驱动
3.获取数据库的连接对象 Connection
4.定义SQL
5.获取执行SQL语句的对象 Statement
6.执行SQL,接收返回结果
7.处理结果
8.释放资源
*/
public class JDBC_1 {
public static void main(String[] args) throws Exception {
//1.导入驱动jar包
//2.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//3.获取数据库的连接对象 Connection
//Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "yzh521");
//本机可省略域名和端口号
Connection conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "yzh521");
//4.定义SQL
String sql = "update account set balance = 500 where id = 1";
//5.获取执行SQL语句的对象 Statement
Statement statement = conn.createStatement();
//6.执行SQL,接收返回结果
int count = statement.executeUpdate(sql);
//7.处理结果
System.out.println(count);
//8.释放资源
statement.close();
conn.close();
}
}
String sql = "insert into account values(null,'WW',3000)";
===========================================================
public class JDBC_2 {
public static void main(String[] args) {
//提升作用域
Statement statement = null;
Connection conn = null;
//注册驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//定义sql
String sql = "insert into account values(null,'WW',3000)";
//获取Connection对象
conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "yzh521");
//获取执行sql的对象
statement = conn.createStatement();
//执行sql
int count = statement.executeUpdate(sql);//影响的行数
//处理结果
System.out.println(count);
if (count>0) {
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//避免空指针异常,先判断statement不等于null
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
ResultSet基本使用,进行查询
public class JDBC_5 {
public static void main(String[] args) {
Statement state = null;
Connection conn = null;
ResultSet resultSet = null;
//1.注册启动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//2.定义sql
String sql = "select * from account";
//3.获取Collection对象
conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "yzh521");
//4.获取定义SQL对象
state = conn.createStatement();
//5.执行SQL
resultSet = state.executeQuery(sql);
//6.处理结果
//6.1让游标向下移动一行
//判断是否有数据
/*if (resultSet.next()){
//6.2获取数据
int id = resultSet.getInt(1);
String name = resultSet.getString("name");
double balance = resultSet.getDouble("balance");
System.out.println(id + "----" + name + "----" +balance);
}*/
//循环遍历
while (resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString("name");
double balance = resultSet.getDouble("balance");
System.out.println(id + "----" + name + "----" +balance);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.closeResource(resultSet, state, conn);
}
}
}
占位符问题
/*
PreparedStatement来完善练习
PreparedStatement:执行sql的对象
1.SQL注入问题:在拼接sq1时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题
输入用户随便,输入密码:a'or'a'='a
sq1:select * from user where username ='fhdsjkf'and password ='a'or 'a'= 'a
2.解决sql注入问题: 使用Preparedstatement对象 来解决
3.预编译的SOL: 参数使用 ? 作为占位符
4.步骤:
1导入驱动jar包mysql-connector-java-5.1.37-bin.jar
2.注册驱动
3.获取数据库连接对象Connection
4.定义sql
注意:sql的参数使用?作为占位符。如:select * from user where username =? and password = ?;
5.获取执行sql语句的对象Preparedstatement Connection.preparestatement(string sql)
6.给?赋值:
方法:setXxx(参数1,参数2)
*参数1:?的位置编号 从1开始
*参数2:?的值
7执行sql,接受返回结果,不需要传递sql语句
8.处理结果
9.释放资源
*/
public class JDBC_6_2test {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.next();
System.out.println("请输入密码:");
String password = sc.next();
boolean flag = new JDBC_6_2test().login(username, password);
if (flag){
System.out.println("登陆成功!");
}else{
System.out.println("用户名或密码错误!");
}
}
public boolean login(String username,String password){
if (username == null || password == null){
return false;
}
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
// 定义sql!!!!!!!!!!!!!
String sql = "select * from user where username = ? and password =?";
// 获取执行sql的对象!!!!!!!!!!!!
preparedStatement = conn.prepareStatement(sql);
// 给?赋值!!!!!!
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
// 执行sql!!!!!!!!!!!!
resultSet = preparedStatement.executeQuery();
// 判断
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(resultSet,preparedStatement,conn);
}
return false;
}
}
JDBC工具类
package Utils;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* JDBC工具类
*
*/
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
//文件的读取,只需要读取一次即可拿到这些值,使用静态代码块
static {
try {
//1. 创建Properties对象
Properties pro=new Properties();
//获取src路径下的文件的方式 --> ClassLoader 类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");
assert resource != null;
String path = resource.getPath();
//2. 加载文件
pro.load(new FileReader(path));
//3. 获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
String driver = pro.getProperty("driver");
//4.注册驱动
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
//释放资源
public static void close(ResultSet resultSet,Statement statement, Connection conn){
closeResource(resultSet, statement, conn);
}
public static void close(Statement statement,Connection conn){
closeResource(null,statement,conn);
}
public static void closeResource(ResultSet resultSet, Statement statement, Connection conn) {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
数据库连接池工具类
/*
Druid连接池的工具类
*/
public class JDBCUtils {
// 1.定义成员变量
private static DataSource ds;
static {
try {
// 2.加载配置文件
Properties pro = new Properties();
InputStream rs = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(rs);
// 3.获取DataSource数据库连接池对象!!!!
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
}
// 4.获取连接Connection对象!!!!!
public static Connection getConnection() throws Exception {
return ds.getConnection();
}
// 5.释放资源
public static void close(ResultSet rs,Statement stat,Connection conn) {
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Statement stat,Connection conn){
close(null,stat,conn);
}
// 6.获取连接池的方法
public static DataSource getDataSource(){
return ds;
}
}
JDBCTemplate
增删改
public class JdbcTemplateDemo2 {
//Junit单元测试,可以让方法独立执行
// 1.获取JFBCTemplate对象,===作为成员变量,private修饰本类使用,方便方法使用,简化代码
private JdbcTemplate JT = new JdbcTemplate(JDBCUtils.getDataSource());
//1.修改1号数据的salary为10000
@Test //====注解,独立运行
public void test1(){
// 2.定义sql
String sql = "update emp set salary = 10000 where id = 1001";
// 3.执行sql
int count = JT.update(sql);
System.out.println(count);
}
//2.添加一条记录"如来"
@Test
public void test2(){
String sql = "insert into emp (id,ename,dept_id) values (?,?,?)";
int count = JT.update(sql,1006,"如来",10);
System.out.println(count);
}
//3.删除如来
@Test
public void test3(){
String sql = "delete from emp where id = ?";
int count = JT.update(sql, 1006);
System.out.println(count);
}
}
查
/**
* 用JdbcTemplate实现查询
*/
public class JdbcTemplateDemo3 {
//Junit单元测试,可以让方法独立执行
// 1.获取JFBCTemplate对象,===作为成员变量,private修饰本类使用,方便方法使用,简化代码
private JdbcTemplate JT = new JdbcTemplate(JDBCUtils.getDataSource());
//1.查询id为1001的记录,将其封装为Map
//列名作为 key ,值作为 value。记录封装为map集合
//查询结果集的长度只能是 1
@Test
public void test4(){
String sql = "select * from emp where id = ? ";
Map<String, Object> map = JT.queryForMap(sql, 1001);
System.out.println(map);
//{id=1001, ename=孙悟空, job_id=1, mgr=1004, joindate=2001-02-09, salary=10000.00, bonus=null, dept_id=10}
}
//2.查询所有记录,将其封装为List
//将每一条记录封装为一个map集合,再将map集合装载到List集合
@Test
public void test5(){
String sql = "select * from emp ";
List<Map<String, Object>> list = JT.queryForList(sql);
for (Map<String, Object> stringObjectMap : list) {
System.out.println(stringObjectMap);
}
}
//3.查询所有记录,封装为Emp对象
//一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
//数据类型变成封装类
@Test
public void test6(){
String sql = "select *from emp";
List<Emp> list = JT.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
for (Emp emp : list) {
System.out.println(emp);
}
}
//4.查询总记录数
//一般用于聚合函数的查询
@Test
public void test7(){
String sql = "select count(id) from emp";
Long total = JT.queryForObject(sql, Long.class);
System.out.println(total);
}
}