想学 javaJDBC ? 来这里我手把手教你
2021/7/16 17:06:37
本文主要是介绍想学 javaJDBC ? 来这里我手把手教你，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
JavaJDBC学习
JDBC（Java DataBase Connectivity）java数据库连接
● 是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它由一组用Java语言编写的类和接口组成。
● 有了JDBC,java开发人员只需要编写一次程序,就可以访问不同的数据库.
对于JDBC API:
供程序员调用的接口与类，集成在java.sql包中
DriverManager类作用：管理各种不同的jDBC驱动
Connection 接口 与特定数据库的连接
Statement 接口 执行sql
PreparedStatement接口 执行sql
ResultSet接口 接收查询结果
对于JavaJDBC程序的搭建:
第一步:
需要初始化驱动程序，这样就可以打开与数据库的通信信道。
Class.forName(“com.mysql.cj.jdbc.Driver”); //反射实现 or DriverManager.registerDriver(new Driver());
第二步:
建立与数据库连接：
这需要使用DriverManager.getConnection()方法来创建一个
Connection对象，它代表一个物理连接的数据库.
Connection con = DriverManager.getConnection(URL,USER,PASS); URL:jdbc:mysql://ip(127.0.0.1):端口(3306)/数据库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC USER:本地数据库用户名(root) PASS:本地数据库密码
第三步:
获得Satement执行sql语句
Statement st = connection.createStatement();
Satement中的方法: Int executeUpdate(String sql) 用于执行ddl语句和dml(增,删,改)语句 返回操作的行数
用于执行ddl语句返回0
用于执行dml语句返回操作的行数
ResultSet executeQuery(String sql); 用于执行查询语句 返回一个ResultSet 集合
示例 :
public static void main(String[] args) {
try {
//加载mysql驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//连接数据库
Connection connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
// System.out.println(connection);
//发送SQL到数据库
Statement st= connection.createStatement();
st.executeUpdate("insert into course (course)values ('php')");//向数据库中插入数据
//中断传输
st.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
接下来我们开始利用IDEA进行对数据库表中数据的更改
从最基本的利用数据库添加数据开始
public class JDBCDemo1 {
public static void main(String[] args) throws SQLException {
JDBCDemo1 jdbcDemo1=new JDBCDemo1();
jdbcDemo1.student("tom","男","2020-2-2");
}
private void student(String name,String sex,String birthday) throws SQLException {
Connection connection=null;
Statement st=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
st=connection.createStatement();
int row = st.executeUpdate("INSERT INTO t_student(NAME,sex,birthday,reg_time)" +
" VALUES('"+name+"','"+sex+"','"+birthday+"',now())");
System.out.println(row);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
finally {
connection.close();
st.close();
}
}
}
以上的方法适用于初学, 不便于向数据库中添加多条数据,通过自定义Student方法,可以提高添加的方法和效率
获得PrepareStatement执行sql语句
● 在sql语句中参数位置使用占位符,使用setXX方法向sql中设置参数
PrepareStatement ps = connection.prepareStatement(sql);
PrepareStatement中的方法:
Int executeUpdate() 用于执行ddl语句和dml(增,删,改)语句 返回操作的行数
用于执行ddl语句返回0
用于执行dml语句返回操作的行数
ResultSet executeQuery(); 用于执行查询语句 返回一个ResultSet 集合
public class Student {
private int id;
private String name;
private String sex;
private Date birthday;
private Date reg_time;
private int phone;
private int height;
private int weight;
private int score;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday(Timestamp birthday) {
return this.birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Date getReg_time(Timestamp reg_time) {
return this.reg_time;
}
public void setReg_time(Date reg_time) {
this.reg_time = reg_time;
}
public int getPhone() {
return phone;
}
public void setPhone(int phone) {
this.phone = phone;
}
public int getHeight(int height) {
return this.height;
}
public void setHeight(int height) {
this.height = height;
}
public int getWeight(int weight) {
return this.weight;
}
public void setWeight(int weight) {
this.weight = weight;
}
public int getScore(int score) {
return this.score;
}
public void setScore(int score) {
this.score = score;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", reg_time=" + reg_time +
", phone=" + phone +
", height=" + height +
", weight=" + weight +
", score=" + score +
'}';
}
}
public class JDBCDemo5 {
public static void main(String[] args) throws SQLException {
JDBCDemo5 jdbcDemo5=new JDBCDemo5();
jdbcDemo5.student("tom","男","2000-3-3","2000-3-3");
}
private void student(String name, String sex,Object birthday,Object reg_time) throws SQLException {
Connection connection=null;
PreparedStatement pt=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
pt=connection.prepareStatement("insert into t_student(name,sex,birthday,reg_time)values(?,?,?,?)");//?表示占位符,
pt.setString(1,name);
pt.setString(2,sex);
pt.setObject(3,birthday);
pt.setObject(4,reg_time);
pt.executeUpdate();
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
finally {
if(connection!=null){
connection.close();
}
if(pt!=null){
pt.close();
}
}
}
}
其中PreparedStatement的第一次执行消耗是很高的. 它的性能体现在后面的重复执行. 使用PreparedStatement的方式来执行一个针对数据库表的查询. JDBC驱动会发送一个网络请求到数据解析和优化这个查询. 而执行时会产生另一个网络请求. 在JDBC驱动中，减少网络通讯是最终的目的. 如果我的程序在运行期间只需要一次请求, 那么就使用Statement. 对于Statement, 同一个查询只会产生一次网络到数据库的通讯. 这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次,最重要的一点是极大地提高了安全性. 防止sql注入
进行删除操作
public class JDBCDemo2 {
public static void main(String[] args) throws SQLException {
JDBCDemo2 jdbcDemo2=new JDBCDemo2();
jdbcDemo2.student(1);
}
private void student(int id) throws SQLException {
Connection connection=null;
Statement st=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
st=connection.createStatement();
int row = st.executeUpdate("delete from number where Dept_id="+id);
int row1=st.executeUpdate("delete from dept where id="+id);
System.out.println(row);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
finally {
connection.close();
st.close();
}
}
}
注意:在进行删除操作时 , 如果有多表关联,需要优先删除与其相关联的从表,否则无法进行删除操作
利用PreparedStatement方法执行操作时
public class JDBCDemo6 {
public static void main(String[] args) throws SQLException {
JDBCDemo6 jdbcDemo5=new JDBCDemo6();
jdbcDemo5.student(10);
}
private void student(int id) throws SQLException {
Connection connection=null;
PreparedStatement pt=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
pt=connection.prepareStatement("delete from t_student where id=?");
pt.setObject(1,id);
pt.executeUpdate();
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();}
finally {
if(connection!=null){
connection.close();
}
if(pt!=null){
pt.close();
}
}
}
}
进行数据库消息的更改:
public class JDBCDemo4 {
public static void main(String[] args) throws SQLException {
JDBCDemo4 jdbcDemo3=new JDBCDemo4();
jdbcDemo3.student(8,"jy","女","2000-12-13");
}
private void student(int id,String name,String sex,String birthday) throws SQLException {
Connection connection=null;
Statement st=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
st=connection.createStatement();
int row = st.executeUpdate("update t_student set name='"+name+"',sex='"+sex+"',birthday='"+birthday+"'where id="+id);
System.out.println(row);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
finally {
connection.close();
st.close();
}
}
}
利用PreparedStatement方法执行删除操作时:
public class JDBCDemo7 {
public static void main(String[] args) throws SQLException {
JDBCDemo7 jdbcDemo5=new JDBCDemo7();
jdbcDemo5.student("1 or 1=1");
}
private void student(String id) throws SQLException {
Connection connection=null;
PreparedStatement pt=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
pt=connection.prepareStatement("delete from t_area where id=?");
pt.setObject(1,id);
pt.executeUpdate();
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();}
finally {
if(connection!=null){
connection.close();
}
if(pt!=null){
pt.close();
}
}
}
}
public class JDBCDemo7b {
public static void main(String[] args) throws SQLException {
JDBCDemo7b jdbcDemo2=new JDBCDemo7b();
jdbcDemo2.student("1 or 1=1");
}
private void student(String id) throws SQLException {
Connection connection=null;
Statement st=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
st=connection.createStatement();
int row = st.executeUpdate( "delete from t_area where id = 1 or 1 = 1");
System.out.println(row);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
finally {
connection.close();
st.close();
}
}
}
其中JDBCDemo7的线程,他会进行检测SQL攻击,在通过set方法在设置值时,会进行检测,如果传入其他关键字时,就会显示异常
在JDBCDemo7b中他无法对传入的值进行检验,在满足1=1的条件后会执行对数据库的清除操作对数据库整体进行清空,执行完成后会返回 0.
利用PreparedStatement方法执行对数据库查找操作时
public class JDBCDemo8 {
public static void main(String[] args) throws SQLException {
JDBCDemo8 jdbcDemo1=new JDBCDemo8();
jdbcDemo1.student(8);
}
private void student(int id) throws SQLException {
Connection connection=null; //连接数据库
PreparedStatement pt=null;
Student student=new Student();
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
pt=connection.prepareStatement("\n" +
"SELECT\n" +
" id,\n" +
" NAME,\n" +
" sex,\n" +
" birthday,\n" +
" reg_time,\n" +
" phone,\n" +
" height,\n" +
" weight,\n" +
" score\n" +
"FROM\n" +
" t_student\n" +
"WHERE id = ?\n");
pt.setInt(1,id);
ResultSet res=pt.executeQuery();
while(res.next()){
student.setId(res.getInt("id"));
student.setName(res.getString("name"));
student.setSex(res.getString("sex"));
student.getBirthday(res.getTimestamp("birthday"));
student.getReg_time(res.getTimestamp("reg_time"));
student.getHeight(res.getInt("height"));
student.getWeight(res.getInt("weight"));
student.getScore(res.getInt("score"));
}
System.out.println(student);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
finally {
connection.close();
pt.close();
}
}
}
如果要同时查找多条数据时,我们可以利用集合进行查找:
public class JDBCDemo9 {
public static void main(String[] args) throws SQLException {
JDBCDemo9 jdbcDemo1=new JDBCDemo9();
jdbcDemo1.student("男");
}
private ArrayList<Student>student(String sex) throws SQLException {
Connection connection=null; //连接数据库
PreparedStatement pt=null;
ArrayList<Student>list=new ArrayList();
Student student=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
pt=connection.prepareStatement("\n" +
"SELECT\n" +
" id,\n" +
" NAME,\n" +
" sex,\n" +
" birthday,\n" +
" reg_time,\n" +
" phone,\n" +
" height,\n" +
" weight,\n" +
" score\n" +
"FROM\n" +
" t_student\n" +
"WHERE sex = ?\n");
pt.setString(1,sex);
ResultSet res=pt.executeQuery();
while(res.next()){
student=new Student();
student.setId(res.getInt("id"));
student.setName(res.getString("name"));
student.setSex(res.getString("sex"));
student.getBirthday(res.getTimestamp("birthday"));
student.getReg_time(res.getTimestamp("reg_time"));
student.getHeight(res.getInt("height"));
student.getWeight(res.getInt("weight"));
student.getScore(res.getInt("score"));
list.add(student);
}
System.out.println(list);
} catch (SQLException | ClassNotFoundException throwables) {
throwables.printStackTrace();
}
finally {
connection.close();
pt.close();
}
return list;
}
}
使用ResultSet中的next()方法获得下一行数据
使用getXXX(String name)方法获得值
这篇关于想学 javaJDBC ? 来这里我手把手教你的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-14动态路由项目实战：从入门到上手
- 2024-11-14函数组件项目实战：从入门到简单应用
- 2024-11-14获取参数项目实战：新手教程与案例分析
- 2024-11-14可视化开发项目实战：新手入门教程
- 2024-11-14可视化图表项目实战：从入门到实践
- 2024-11-14路由懒加载项目实战：新手入门教程
- 2024-11-14路由嵌套项目实战：新手入门教程
- 2024-11-14全栈低代码开发项目实战：新手入门指南
- 2024-11-14全栈项目实战：新手入门教程
- 2024-11-14useRequest教程：新手快速入门指南
