【JDBC】
JDBC
JAVA连接数据库的一种规范
导入驱动
到度娘查询下载对应的数据库的驱动——关键字数据库驱动。
下载后的文件包里会有帮助文档，上面有详细的使用教程和案例（docs），自己看着文档去一步步学习会印象深刻。
创建新文件夹bin将这个jar包粘贴进去——build path——add build path
编写
1.注册驱动（JDBC 4.0 后可以省略不写）
- DriverManager.registerDriver( )
- 里面new一个Driver（注意这里是com.mysql的）
- try catch一下
//1.注册驱动
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException e) {
e.printStackTrace();
}
2.建立连接
- DriverManager.getConnection( )
- 有三种连接方式一般使用如下两种填好url,username&password
- 会得到一个connection对象前面接一下
//2.建立连接
//DriverManager.getConnection("jdbc:mysql://localhost/paper?user=root&password=123456");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/paper", "root", "123456");
3.创建一个Statement对象
- 跟数据库打交道必须要用这个对象
- connection的createStatement( )创建
//3.创建一个Statement对象
Statement st = con.createStatement();
4.执行查询,得到结果集
- statement的executeQuery( )
- 中间填入sql语句执行
//4.执行查询,得到结果集
String sql = "select * from product";
ResultSet rs = st.executeQuery(sql);
5.循环遍历
- 执行后得到的resultSet结果集需要遍历显示
- 结果集的最后一条信息的下一条为空NULL
- 用rs.next指向下一条，为空时跳出循环
- rs.getXX( )中间为字段名，可以获取不同类型的字段值
while(rs.next()){
double price = rs.getDouble("price");
String name = rs.getString("pname");
System.out.println("商品："+name+"价格"+price);
}
6.关闭连接
- 注意这里放在finally里且加上判断是否为null再关闭
rs.close();
st.close();
con.close();
执行后结果
JDBCUtil构建
资源释放整合
- 加上为空判断
package com.test.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtil {
public static void release(Connection conn,ResultSet rs,Statement st){
closeConn(conn);
closeRs(rs);
closeSt(st);
}
private static void closeRs(ResultSet rs){
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeSt(Statement st){
try {
if(st != null){
st.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeConn(Connection conn){
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
驱动防止二次注册
//1.注册驱动
DriverManager.registerDriver(new Driver());
- 这句代码在JDBC的静态代码块中已经写过了
//1.注册驱动
Class.forName("clcom.mysql.jdbc.Driver.classassName");
- copy qualified name复制全路径
连接对象整合
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtil {
static String url = "jdbc:mysql://localhost/paper";
static String username = "root";
static String password = "123456";
/**
* 获取连接对象
* @return
*/
public static Connection getConn(){
Connection conn = null;
try {
//1.注册驱动(DriverManager.registerDriver,里面new一个driver注意是com.mysql的driver，然后TRY catch一下)
//DriverManager.registerDriver(new Driver());
Class.forName("com.mysql.jdbc.Driver");
//2.建立连接(DriverManager.getConnection,填好url,username&password,得到一个connection对象)
//DriverManager.getConnection("jdbc:mysql://localhost/paper?user=root&password=123456");
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void release(Connection conn,ResultSet rs,Statement st){
closeConn(conn);
closeRs(rs);
closeSt(st);
}
private static void closeRs(ResultSet rs){
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeSt(Statement st){
try {
if(st != null){
st.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void closeConn(Connection conn){
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
JDBC配置文件
- 在src下建立文件jdbc.properties,这样加载器编译的时候才会在bin目录下
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream(“jdbc.properties”); - 写在工程根目录下用这种方式也可以读取到
InputStream is = new FileInputStream(“jdbc.properties”);
driverClass = com.mysql.jdbc.Driver
url =jdbc:mysql://localhost/paper
username = root
password = 123456
- 在JDBCUtil的静态代码块中读取配置文件
static{
try {
//1.创建一个属性配置文件
Properties properties = new Properties();
//InputStream is = new FileInputStream("jdbc.properties");
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
//2.导入输入流
properties.load(is);
//读取属性
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (Exception e) {
}
}
代码
package com.test.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Maintest {
public static void main(String[] args) throws ClassNotFoundException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql = "select * from product";
rs = st.executeQuery(sql);
while(rs.next()){
double price = rs.getDouble("price");
String name = rs.getString("pname");
System.out.println(name+" "+price);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtil.release(conn, st, rs);
}
}
}
JDBCUtil
package com.test.jdbc;
import java.io.IOException;
import java.io.InputStream;
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 {
static String driverClass = null;
static String url = null;
static String username = null;
static String password = null;
static{
try {
Properties properties = new Properties();
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(is);
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driverClass);
conn = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void release(Connection conn,Statement st,ResultSet rs){
closeConn(conn);
closeSt(st);
closeRs(rs);
}
private static void closeConn(Connection conn){
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
private static void closeSt(Statement st){
try {
if(st != null){
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
private static void closeRs(ResultSet rs){
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
}
jdbc.properties
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost/paper
username = root
password = 123456
测试 —— junit单元测试
- 定义一个TestXXX类
- 添加junit的支持
- 在要测试的方法上添加@Test注解
- 右键以junit方式运行
增删改查代码整合
package com.test.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
public class TestDemo {
@Test
public void testQuery(){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//获得连接
conn = JDBCUtil.getConn();
//创建Statement对象
st = conn.createStatement();
//执行sql语句获得结果集
String sql = "select * from product";
rs = st.executeQuery(sql);
//遍历结果集
while(rs.next()){
String name = rs.getString("pname");
double price = rs.getDouble("price");
System.out.println(name+"-----"+price);
}
} catch (Exception e) {
// TODO: handle exception
}finally{
JDBCUtil.release(conn, st, rs);
}
}
@Test
public void testInsert(){
Connection conn = null;
Statement st = null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql = "insert into product values(null,'北京方便面',2,16);";
int result = st.executeUpdate(sql);
if(result > 0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
} catch (Exception e) {
}finally{
JDBCUtil.release(conn, st);
}
}
public void testDelete(){
Connection conn = null;
Statement st = null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql = "delete from product where pname like '旺仔牛奶%';";
int result = st.executeUpdate(sql);
if(result > 0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (Exception e) {
}finally{
JDBCUtil.release(conn, st);
}
}
public void testUpdate(){
Connection conn = null;
Statement st = null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql = "update product set price = 1 where pname = '北京方便面';";
int result = st.executeUpdate(sql);
if(result > 0){
System.out.println("更新成功");
}else{
System.out.println("更新失败");
}
} catch (Exception e) {
}finally{
JDBCUtil.release(conn, st);
}
}
}
扫一扫
9310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
