java连接数据库常见错误_JDBC原理及常见错误分析

最新推荐文章于 2022-04-11 18:45:29 发布

分秒帧

最新推荐文章于 2022-04-11 18:45:29 发布

阅读量756

点赞数

文章标签： java连接数据库常见错误

本文链接：https://blog.csdn.net/weixin_28937075/article/details/114960630

版权

interface PreparedStatement extends Statement

因此

增删改：executeUpdate()

查询：executeQuery();

--此外

赋值操作 setXxx();

PreparedStatement与Statement在使用时的区别：

1.Statement:

sql

executeUpdate(sql)

PreparedStatement:

sql(可能存在占位符?)

在创建PreparedStatement 对象时，将sql预编译 prepareStatement(sql)

executeUpdate()

setXxx()替换占位符？

推荐使用PreparedStatement：原因如下：

1.编码更加简便(避免了字符串的拼接)

String name = "zs";int age = 23;

stmt:

String sql=" insert into student(stuno,stuname) values(‘"+name+"‘, "+age+" ) ";

stmt.executeUpdate(sql);

pstmt:

String sql=" insert into student(stuno,stuname) values(?,?) ";

pstmt= connection.prepareStatement(sql);//预编译SQL

pstmt.setString(1,name);

pstmt.setInt(2,age);

2.提高性能(因为有预编译操作，预编译只需要执行一次)

需要重复增加100条数

stmt:

String sql=" insert into student(stuno,stuname) values(‘"+name+"‘, "+age+" ) ";for(100)

stmt.executeUpdate(sql);

pstmt:

String sql=" insert into student(stuno,stuname) values(?,?) ";

pstmt= connection.prepareStatement(sql);//预编译SQL

pstmt.setString(1,name);

pstmt.setInt(2,age);for( 100){

pstmt.executeUpdate();

}

3.安全(可以有效防止sql注入)

sql注入：将客户输入的内容和开发人员的SQL语句混为一体

stmt:存在被sql注入的风险

(例如输入用户名：任意值 ‘ or 1=1 --

密码：任意值)

分析：

select count(*) from login where uname=‘任意值 ‘ or 1=1 --‘ and upwd =‘任意值‘ ;

select count(*) from login where uname=‘任意值 ‘ or 1=1;

select count(*) from login ;

select count(*) from login where uname=‘"+name+"‘ and upwd =‘"+pwd+"‘pstmt:有效防止sql注入

推荐使用pstmt

3.jdbc访问数据库的具体步骤：

导入驱动，加载具体的驱动类

与数据库建立连接

发送sql，执行

处理结果集 (查询)

数据库驱动

驱动jar 具体驱动类连接字符串

MySQL mysql-connector-java-x.jarcom.mysql.jdbc.Driver jdbc:mysql://localhost:3306/数据库实例名

使用jdbc操作数据库时，如果对数据库进行了更换，只需要替换：驱动、具体驱动类、连接字符串、用户名、密码

测试JDBC基本功能代码

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;public classJDBCDemo {private static final String URL = "jdbc:mysql://localhost:3306/mydatabase?serverTimezone=GMT%2B8";private static final String USERNAME = "root";private static final String PWD = "password";public static void update() throws ClassNotFoundException, SQLException {//增删改//a. 导入驱动，加载具体的驱动类

Class.forName("com.mysql.cj.jdbc.Driver");//b.与数据库建立连接

Connection connection =DriverManager.getConnection(URL, USERNAME, PWD);//c.发送sql，执行增删改查

Statement stmt =connection.createStatement();//增加 String sql = "insert into student values(2,‘李四‘,21)";//修改 String sql = "update student set name=‘张三‘ where id=1";//删除

String sql = "delete from student where id=1";int count =stmt.executeUpdate(sql);if (count > 0) {

System.out.println("操作成功！");

}

stmt.close();

connection.close();

}public static void query() throws ClassNotFoundException, SQLException {//增删改//a. 导入驱动，加载具体的驱动类

Class.forName("com.mysql.cj.jdbc.Driver");//b.与数据库建立连接

Connection connection =DriverManager.getConnection(URL, USERNAME, PWD);//c.发送sql，执行增删改[查]

Statement stmt =connection.createStatement();char stuname=‘a‘;//模糊查询

String sql = "select id,name,age from student where name like ‘%"+stuname+"%‘";

ResultSet rs=stmt.executeQuery(sql);//int count = stmt.executeUpdate(sql);

while(rs.next()) {int id=rs.getInt("id");

String name=rs.getString("name");int age=rs.getInt("age");

System.out.println(id+"--"+name+"--"+age);

}

rs.close();

stmt.close();

connection.close();

}public static void main(String[] args) throwsClassNotFoundException, SQLException {//update();

query();

}

错误记录：

Exception in thread "main" java.sql.SQLException: The server time zone value ‘?й???????‘ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value ifyou want to utilize time zone support.

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)

at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)

at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:455)

at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)

at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)