JDBC的连接与sql增删查改
概述:JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
以mysql为例:
前提准备:
在mysql数据库创建数据库叫school,一个表叫studnt
表字段有:id name sex age mianmao
导入mysql-connector-java-5.1.19.jar,网上自行下载即可
导入方法:将jar包放到src下,然后右键jar包,点击BuildPath,然后点击add…即可
创建JDBC的步骤
1.加载JDBC驱动
Class.forName("com.mysql.jdbc.Driver");
2.建立URL,连接数据库
String url="jdbc:mysql://localhost:3306/school"; Connection conn=DriverManager.getConnection(url,"root","root");
注意:jdbc:mysql://localhost:3306/数据库名称
(url,“root”,“root”)后两个参数分别是数据库账号和密码;
3.创建statement对象
Statement st= conn.createStatement();
4.执行查询或更新
String sql="select * from student ";
5.操作结果集
ResultSet rs=st.executeQuery(sql);
循环遍历结果集
while(rs.next()){
int id=rs.getInt(1);
String name=rs.getString(2);
String sex=rs.getString(3);
int age=rs.getInt(4);
String mianmao=rs.getString(5);
System.out.println(id+" "+name+" "+sex+" "+age+" "+mianmao);
}
6.释放资源
rs.close();
st.close();
conn.close();
需要导的包:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
注意是import java.sql下的包
以下为完整代码:
1.查询所有学生
package com.tao.test;
/**
* @author 天外飞星
*
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test01 {
/**
*
* @param args
* @throws ClassNotFoundException
* @throws SQLException
*@since 2018-11-13
*/
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立URL,连接数据库
String url="jdbc:mysql://localhost:3306/school";
Connection conn=DriverManager.getConnection(url,"root","root");
//3.创建statement对象
Statement st= conn.createStatement();
//4.执行查询或更新
String sql="select * from student ";
//5.操作结果集
ResultSet rs=st.executeQuery(sql);
while(rs.next()){
int id=rs.getInt(1);
String name=rs.getString(2);
String sex=rs.getString(3);
int age=rs.getInt(4);
String mianmao=rs.getString(5);
System.out.println(id+" "+name+" "+sex+" "+age+" "+mianmao);
}
//6.释放资源
rs.close();
st.close();
conn.close();
}
}
2.删除id为7的学生
package com.tao.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立URL,连接数据库
String url="jdbc:mysql://localhost:3306/school?characterEncoding=utf-8";
Connection conn=DriverManager.getConnection(url,"root","root");
//3.创建statement对象
Statement st= conn.createStatement();
//4.执行查询或更新
String sql="delete from student where id=7";
//5.操作结果集
int row=st.executeUpdate(sql);
if(row==1){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
//6.释放资源
st.close();
conn.close();
}
}
3.插入一条记录
package com.tao.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立URL,连接数据库
String url="jdbc:mysql://localhost:3306/school?characterEncoding=utf-8";
Connection conn=DriverManager.getConnection(url,"root","root");
//3.创建statement对象
Statement st= conn.createStatement();
//4.执行查询或更新
String sql="insert into student values(8,'Jack2','女',22,'群众')";
//5.操作结果集
int row=st.executeUpdate(sql);
if(row==1){
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
//6.释放资源
st.close();
conn.close();
}
}
4.更改id=6的学生性别为男
package com.tao.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test04 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.建立URL,连接数据库
String url="jdbc:mysql://localhost:3306/school?characterEncoding=utf-8";
Connection conn=DriverManager.getConnection(url,"root","root");
//3.创建statement对象
Statement st= conn.createStatement();
//4.执行查询或更新
String sql="update student set sex='男' where id=6";
//5.操作结果集
int row=st.executeUpdate(sql);
if(row==1){
System.out.println("更改成功");
}else {
System.out.println("更改失败");
}
//6.释放资源
st.close();
conn.close();
}
}