一、什么是JDBC?
JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序,同时,JDBC也是个商标名。
二、准备环境
连接数据库使用的是Mysql5.5、Navicat Premium 15
连接数据库需要使用jar包mysql-connector-java-5.0.8-bin
jar包下载教程
三、实例
首先创建一个名为wzsxy的数据库
主键为id 自增长
首先是单独的Untl类,便于修改
getconnection方法用于连接数据库,claseAll用于释放。
public class DBUtil {
public static Connection getconnection() throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wzsxy", "root", "123456");
return connection;
}
public static void closeAll(ResultSet resultSet, Statement statement,Connection connection)throws SQLException{
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
(1)查询功能
public class Find {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
ResultSet resultSet=null;
PreparedStatement statement=null;
Connection connection=null;
try {
connection= DBUtil.getconnection();
//写sql语句
String sql="select * from tb_usr ";
//获得stat对象
statement=connection.prepareStatement(sql);
//执行sql语句得到结果表
resultSet=statement.executeQuery();
//处理结果集合
while (resultSet.next()){
System.out.println(resultSet.getInt(1));
System.out.println(resultSet.getString(2));
System.out.println(resultSet.getString(3));
}
//关闭集合
DBUtil.closeAll(resultSet,statement,connection);
}catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
(2)修改
修改id为4的密码为123456
public class Update {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
ResultSet resultSet=null;
Statement statement=null;
Connection connection=null;
try {
connection= DBUtil.getconnection();
// 执行SQL
String sql = "update tb_usr set password = 123456 where id = 4";
statement = connection.createStatement();
// 返回结果
int result = statement.executeUpdate(sql);
System.out.println("受影响行数: " + result);
//关闭集合
DBUtil.closeAll(resultSet,statement,connection);
}catch (Exception e) {
e.printStackTrace();
}
}
}
(3)刪除
刪除id是7的所有信息
public class Delete{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
ResultSet resultSet=null;
PreparedStatement statement=null;
Connection connection=null;
try {
connection= DBUtil.getconnection();
//写sql语句
String sql="delete from tb_usr where id=7";
//获得stat对象
statement=connection.prepareStatement(sql);
//执行sql语句得到结果表
statement.executeUpdate();
// 返回结果
System.out.println("完成删除");
//关闭集合
DBUtil.closeAll(resultSet,statement,connection);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
(4)增加
增加一条新的数据
public class Insert{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
ResultSet resultSet=null;
Statement statement=null;
Connection connection=null;
try {
connection= DBUtil.getconnection();
//执行SQl
String sql = "insert into tb_usr(username, password) values ('Joel','2020619')";
statement = connection.createStatement();
int resultRows = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
System.out.println("受影响行数: "+resultRows);
//关闭集合
DBUtil.closeAll(resultSet,statement,connection);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}