jdbc学习
# 快速入门，看的黑马程序员
package com.itheima.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/xiaohaizi";//xiaohaizi是数据库的名字
String username = "root";
String password = "****";
Connection conn = DriverManager.getConnection(url,username,password);
//3.定义sql
String sql = "update account set money = 2000 where id = 1";
//4.获取执行sql的对象statement
Statement stmt = conn.createStatement();
//5.执行sql
int count = stmt.executeUpdate(sql); //受影响的行数
//6.处理结果
System.out.println(count);
//7.释放资源
stmt.close();
conn.close();
}
}API详解
DriverManager
两个功能：注册驱动、获取数据库的连接
第一行代码Class.forName("com.mysql.jdbc.Driver");可以省略不写
Connection
两个功能：获取执行sql的对象、事务管理
//3.定义sql
String sql1 = "update account set money = 2000 where id = 1";
String sql2 = "update account set money = 3000 where id = 2";
//4.获取执行sql的对象statement
Statement stmt = conn.createStatement();
try {
//开启事务
conn.setAutoCommit(false);
//5.执行sql
int count1 = stmt.executeUpdate(sql1); //受影响的行数
//6.处理结果
System.out.println(count1);
int i = 3/0;
//5.执行sql
int count2 = stmt.executeUpdate(sql2); //受影响的行数
//6.处理结果
System.out.println(count2);
//提交事务
conn.commit();
} catch (Exception e) {
//回滚事务
conn.rollback();
throw new RuntimeException(e);
}Statement
用来执行sql语句
ResultSet
package com.itheima.jdbc;
import java.sql.*;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/xiaohaizi";//xiaohaizi是数据库的名字
String username = "root";
String password = "******";
Connection conn = DriverManager.getConnection(url,username,password);
String sql = "select * from account";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){//当前行如果有效就继续执行
int id = rs.getInt(1);//括号里面是列数，这里的列数从1开始算；或者用列名也可以
String name = rs.getString(2);
double money = rs.getDouble(3);
System.out.println(id);
System.out.println(name);
System.out.println(money);
System.out.println("----------------------------------");
}
//7.释放资源
rs.close();
stmt.close();
conn.close();
}
}package com.itheima.jdbc;
import com.itheima.pojo.Account;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/xiaohaizi";//xiaohaizi是数据库的名字
String username = "root";
String password = "fyw040806";
Connection conn = DriverManager.getConnection(url,username,password);
String sql = "select * from account";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//创建集合
List<Account> list = new ArrayList<>();
while(rs.next()){//当前行如果有效就继续执行
Account account = new Account();
int id = rs.getInt(1);//括号里面是列数，这里的列数从1开始算
String name = rs.getString(2);
double money = rs.getDouble(3);
//赋值
account.setId(id);
account.setMoney(money);
account.setName(name);
//存入集合
list.add(account);
}
System.out.println(list);
//7.释放资源
rs.close();
stmt.close();
conn.close();
}
}package com.itheima.pojo;
public class Account {
private int id;
private String name;
private double money;
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 double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}PreparedStatement
数据库连接池
System.out.println(System.getProperty("user.dir"));找到当前代码针对的路径
package com.itheima.druid;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
public class Druid {
public static void main(String[] args) throws Exception {
//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/Druid.properties"));
//4.获取连接池对象
DataSource dateSource = DruidDataSourceFactory.createDataSource(prop);
//5.获取数据库连接connection
Connection connection = dateSource.getConnection();
System.out.println(connection);
}
}增删查改
package com.itheima.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* 品牌数据的增删改查操作
*/
public class BrandTest {
@Test
public void testSelectAll() throws Exception {
//加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/Druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接
Connection conn = dataSource.getConnection();
//2.定义sql语句
String sql = "SELECT * from tb_brand;";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
//5.执行sql
ResultSet rs = pstmt.executeQuery();
//6.处理结果
List<Brand> list = new ArrayList<>();
while (rs.next()){
int id = rs.getInt("id");
String brandName = rs.getString("brand_name");
String companyName = rs.getString("company_name");
int ordered = rs.getInt("ordered");
String description = rs.getString("description");
int status = rs.getInt("status");
//封装成brand对象
Brand brand = new Brand();
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setStatus(status);
brand.setDescription(description);
list.add(brand);
}
System.out.println(list);
//释放资源
rs.close();
pstmt.close();
conn.close();
}
@Test
/**
* 1.sql
* 2.需要参数
* 3.结果：返回boolean值
* 不需要添加id，这个由数据库自动生成
*/
public void testAddAll() throws Exception {
//接收页面提交的参数
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered = 1;
String description = "绕地球一圈";
int status = 1;
//加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/Druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接
Connection conn = dataSource.getConnection();
//2.定义sql语句
String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
//5.执行sql
int count = pstmt.executeUpdate();//影响的行数
//6.返回结果
System.out.println(count>0);
//释放资源
pstmt.close();
conn.close();
}
@Test
/**
* 1.sql
* 2.需要参数
* 3.结果：返回boolean
*/
public void testUpdateAll() throws Exception {
//接收页面提交的参数
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered = 100;
String description = "绕地球三圈";
int status = 1;
int id = 4;
//加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/Druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接
Connection conn = dataSource.getConnection();
//2.定义sql语句
String sql = "update tb_brand\n" +
" set brand_name = ?,\n" +
" company_name = ?,\n" +
" ordered = ?,\n" +
" description = ?,\n" +
" status = ?\n" +
" where id = ?;";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
pstmt.setInt(6,id);
//5.执行sql
int count = pstmt.executeUpdate();//影响的行数
//6.返回结果
System.out.println(count>0);
//释放资源
pstmt.close();
conn.close();
}
@Test
/**
* 1.sql
* 2.需要参数id
* 3.结果：返回boolean
*/
public void testDeleteById() throws Exception {
//接收页面提交的参数
int id = 5;
//加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("src/Druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接
Connection conn = dataSource.getConnection();
//2.定义sql语句
String sql = "delete from tb_brand where id = ?;";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setInt(1,id);
//5.执行sql
int count = pstmt.executeUpdate();//影响的行数
//6.返回结果
System.out.println(count>0);
//释放资源
pstmt.close();
conn.close();
}
}