增删改查
其余操作只需要修改sql语句
修改信息
String sql = "update student set score=90 where id=3";
删除信息
String sql = "delete from student where id=3";
创建表
String sql = "create table account (id int, name varchar(20))";
创建表的时候 st.executeUpdate(sql) 不返回值
package cn.sdut.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc {
/*
添加一条student记录
*/
public static void main(String[] args) {
Connection con = null;
Statement st = null;
try {
//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2、定义sql
String sql = "insert into student values(3,'张三',65)";
//3、获取Connection执行对象
con = DriverManager.getConnection("jdbc:mysql://localhost:3307/sdut?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
//4、获取sql语句执行对象
st = con.createStatement();
//5、执行sql语句
int count = st.executeUpdate(sql);
System.out.println(count);
//6、处理
if(count > 0)
System.out.println("添加成功");
else
System.out.println("添加失败");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7、释放资源
try {
if(st != null)
st.close();
if(con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
ResultSet:
- 结果集对象,封装查询结果
游标默认位置在第一行之前
游标下判断并移获取数据
- next(): 游标下一一行
- getXxx(参数):
- 例:getInt()
-
1.int:代表列的编号,从1开始 getInt(1) 2.String:代表列表名称 getInt("age")
-
package cn.sdut.jdbc;
import java.sql.*;
public class jdbc {
/*
查询信息
*/
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2、定义sql
String sql = "select * from student";
//3、获取Connection执行对象
con = DriverManager.getConnection("jdbc:mysql://localhost:3307/sdut?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
//4、获取sql语句执行对象
st = con.createStatement();
//5、执行sql语句
rs = st.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int score = rs.getInt("score");
System.out.println(id+"--"+name+"--"+score);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//7、释放资源
try {
if(rs != null)
rs.close();
if(st != null)
st.close();
if(con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
练习
将表中数据封装成对象,装在集合中返回
Student:
package cn.sdut.jdbc;
/*
封装student表数据JavaBean
*/
public class Student {
private int id;
private String name;
private int score;
public Student(int id, String name, int score) {
this.id = id;
this.name = name;
this.score = score;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public int getScore() {
return score;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setScore(int score) {
this.score = score;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", score=" + score +
'}';
}
}
JDBC:
package cn.sdut.jdbc;
import java.sql.*;
import java.util.*;
public class jdbc {
public static void main(String[] args) {
List<Student> list = new jdbc().findAll();
System.out.println(list);
}
/*
将表中数据封装成对象,装在集合中返回
*/
public List<Student> findAll() {
Student stu = null;
List<Student> list = null;
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3307/sdut?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
String sql = "select * from student";
st = con.createStatement();
rs = st.executeQuery(sql);
list = new ArrayList<Student>();
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int score = rs.getInt("score");
stu = new Student(id,name,score);
list.add(stu);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null)
rs.close();
if(st != null)
st.close();
if(con != null)
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return list;
}
}
JDBCUtil工具类:
- 目的:简化书写
- 分析:
-
1.注册驱动也抽取 2.抽取一个方法获取连接对象 需求:不想传递参数,保证工具类的通用性 解决:配置文件
jdbc.properties
url =
user =
password =
src目录下建立jdbc.properties
内容:
url = jdbc:mysql://localhost:3307/sdut?useUnicode=true&characterEncoding=utf-8
user = root
password = usbw
driver = com.mysql.jdbc.Driver
动态获取路径方法:
动态获取src路径下的文件的方式-->ClassLoader(类加载器)
ClassLoader classLoader = JDBCUtil.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
JDBCUtil工具类:
package cn.sdut.jdbc;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class JDBCUtil {
private static String url;
private static String user;
private static String password;
private static String driver;
/*
文件的读取,只需要一次读取就能拿到这些值
使用静态代码块
*/
static {
//读取资源文件,获取值
try {
//1.创建Properties集合类对象
Properties pro = new Properties();
//动态获取src路径下的文件的方式-->ClassLoader(类加载器)
ClassLoader classLoader = JDBCUtil.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
//2.加载文件
pro.load(new FileReader(path));
//3.获取数据,赋值
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4.注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void closeAll(ResultSet rs, Statement st, Connection con) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}