目录
1 JDBC概念
JDBC 就是使用Java语言操作关系型数据库的一套API
全称:( Java DataBase Connectivity ) Java 数据库连接
2快速入门
3.API
3.1DriverManager
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/test";
String username="root";
String password="123";
Connection conn = DriverManager.getConnection(url, username, password);
//定义sql
String sql = "update account set money = 3000 where id = 1";
//获取执行sql对象
Statement statement = conn.createStatement();
//执行sql
int count = statement.executeUpdate(sql);//返回受影响的行数
System.out.println(count);
//释放资源
statement.close();;
conn.close();
}
}
3.2Connection
//jdbcAPI Connection
public class JdbcDemo2_Connection {
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/test";
String username="root";
String password="123";
Connection conn = DriverManager.getConnection(url, username, password);
//定义sql
String sql1 = "update account set money = 3000 where id = 1";
String sql2 = "update account set money = 2000 where id = 2";
//获取执行sql对象
Statement statement = conn.createStatement();
//执行sql
try {
//开启事务
conn.setAutoCommit(false);
int count1 = statement.executeUpdate(sql1);//返回受影响的行数
System.out.println(count1);
//出现异常后会回滚,数据库不修改
// int i = 3/0;
int count2 = statement.executeUpdate(sql2);//返回受影响的行数
System.out.println(count2);
//提交事务
conn.commit();
} catch (Exception e) {
//回滚事务
conn.rollback();
e.printStackTrace();
}
//释放资源
statement.close();;
conn.close();
}
}
//jdbcAPI resultSet
public class JdbcDemo3_resultSet {
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/test";
String username = "root";
String password = "123";
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义sql
String sql = "select * from account";
//4.获取statement对象
Statement statement = conn.createStatement();
//5.执行sql
ResultSet rs = statement.executeQuery(sql);
//6处理结果,遍历rs中所有的数据
//6.1光标向下移动一行,并判断当前行是否有数据
while (rs.next()){
//6.2获取数据
int id = rs.getInt(1);
String name = rs.getString(2);
double money = rs.getDouble(3);
System.out.print(id+" ");
System.out.print(name+" ");
System.out.print(money+" ");
System.out.println();
}
//7.释放资源
rs.close();
statement.close();
conn.close();
}
}
数据库连接池
jdbc练习
对数据库表进行增删改查
表字段
// id 主键
private Integer id;
// 品牌名称
private String brand_name;
// 企业名称
private String company_name;
// 排序字段
private Integer ordered;
// 描述信息
private String description;
// 状态:0:禁用 1:启用
private Integer status;
查询所有数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.lch.pojo.Brand;
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 SelectAll {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("D:\\Java\\javaweb\\jdbc_study\\src\\druid.properties"));
//1.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//2.获取数据库链接Connection
Connection connection = dataSource.getConnection();
//3.定义sql
String sql = "select * from tb_brand";
//4.获取PreparedStatement对象
PreparedStatement statement = connection.prepareStatement(sql);
//5.设置参数
//6.执行sql
ResultSet rs = statement.executeQuery();
//处理结果List<Brand>封装Brand对象,装载List集合
ArrayList<Brand> list = new ArrayList<>();
Brand brand = null;
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 = new Brand();
brand.setId(id);
brand.setBrand_name(brandName);
brand.setCompany_name(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//装载集合
list.add(brand);
}
System.out.println(list);
//7.释放资源
rs.close();
statement.close();
connection.close();
}
}
添加一条数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.lch.pojo.Brand;
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.Properties;
public class Add {
public static void main(String[] args) throws Exception {
String brandName="香飘飘";
String companyName="香飘飘";
int ordered = 1;
String description="绕地球一圈";
int status = 1 ;
Properties properties = new Properties();
properties.load(new FileInputStream("D:\\Java\\javaweb\\jdbc_study\\src\\druid.properties"));
//1.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//2.获取数据库链接Connection
Connection connection = dataSource.getConnection();
//3.定义sql
String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) value(?,?,?,?,?) ";
//4.获取PreparedStatement对象
PreparedStatement statement = connection.prepareStatement(sql);
//5.设置参数
statement.setString(1,brandName);
statement.setString(2,companyName);
statement.setInt(3,ordered);
statement.setString(4,description);
statement.setInt(5,status);
//6.执行sql
int count = statement.executeUpdate();//影响的行数
//输出结果
System.out.println(count > 0);
//释放资源
statement.close();
connection.close();
}
}
修改一条数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class Update {
public static void main(String[] args) throws Exception {
String brandName="香飘飘2";
String companyName="香飘飘2";
int ordered = 1;
String description="绕地球一圈";
int status = 1 ;
int id = 4;
Properties properties = new Properties();
properties.load(new FileInputStream("D:\\Java\\javaweb\\jdbc_study\\src\\druid.properties"));
//1.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//2.获取数据库链接Connection
Connection connection = dataSource.getConnection();
//3.定义sql
String sql = "update tb_brand set brand_name=?,company_name=?,ordered=?,description=?,status=? where" +
" id = ?";
//4.获取PreparedStatement对象
PreparedStatement statement = connection.prepareStatement(sql);
//5.设置参数
statement.setString(1,brandName);
statement.setString(2,companyName);
statement.setInt(3,ordered);
statement.setString(4,description);
statement.setInt(5,status);
statement.setInt(6,id);
//6.执行sql
int count = statement.executeUpdate();
//输出结果
System.out.println(count > 0);
//释放资源
statement.close();
connection.close();
}
}
删除一条数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class Delete {
public static void main(String[] args) throws Exception {
int id = 4;
Properties properties = new Properties();
properties.load(new FileInputStream("D:\\Java\\javaweb\\jdbc_study\\src\\druid.properties"));
//1.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//2.获取数据库链接Connection
Connection connection = dataSource.getConnection();
//3.定义sql
String sql = "delete from tb_brand where id = ?";
//4.获取PreparedStatement对象
PreparedStatement statement = connection.prepareStatement(sql);
//5.设置参数
statement.setInt(1,id);
//6.执行sql
int count = statement.executeUpdate();
//输出结果
System.out.println(count > 0);
//释放资源
statement.close();
connection.close();
}
}