用Java操作数据库时
- 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); - 连接数据库
DriverManager.getConnection(url, username, password); - 获得执行sql的对象
connection.createStatement(); - 获得返回的结果集
resultSet = statement.execute(sql); - 关闭数据库连接，释放资源
import java.sql.*;
public class jdbctest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
String username="root";
String password="123456";
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "SELECT `subjectno`,`subjectname` FROM `subject` WHERE `subjectno` <10";
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("subjectno"+" subjectname");
while (resultSet.next()){
//System.out.println("subjectno"+" subjectname");
System.out.println(" "+resultSet.getObject("subjectno")+" "+resultSet.getObject("subjectname"));
//System.out.println("=====================");
}
resultSet.close();
statement.close();
connection.close();
}
复制代码注：这是mysql 8.0以上版本的操作，com.mysql.cj.jdbc.Driver和url的serverTimezone=Asia/Shanghai，与早期版本的有些区别
对象的解释
Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动这是8.0版本后的驱动
String url="jdbc:mysql://localhost:3306/school?"+
"useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
String username="root";
String password="123456";
复制代码url格式jdbc:mysql://localhost:3306/数据库名?/参数1&参数2/serverTimezone=Asia/Shanghai 8.0版本的时区要特别注意一下
Connection connection = DriverManager.getConnection(url, username, password);
复制代码Connection就代表数据库，常见的操作有
connection.commit();//事务提交
connection.rollback(); //事务回滚
connection.setAutoCommit();//事务自动提交
Statement PrepareStatement是具体的执行sql的对象
String sql = "SELECT subjectno,subjectname FROM subject WHERE subjectno <10";
statement.execute(sql);//执行任何sql语句
int num = statement.executeUpdate(sql);//执行更新、插入、删除的sql，返回受影响的行数（int）
statement.executeQuery(sql);//执行查询的sql语句
ResultSet 返回查询的结果，封装了所有结果集
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
//columnLabel=数据库表中的列名
resultSet.getObject(columnLabel);
resultSet.getString(columnLabel);
resultSet.getInt(columnLabel);
resultSet.getFloat(columnLabel);
resultSet.getDate(columnLabel);
}
复制代码释放资源
resultSet.close();
statement.close();
connection.close();
将连接数据库的操作进行封装
先了解一下如何获取配置文件.properties
先创建配置文件db.properties
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
username=root
driver=com.mysql.cj.jdbc.Driver
password=123456
复制代码然后创建Utils
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String url=null;
private static String username=null;
private static String password=null;
private static String driver=null;
static {
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");//类加载器
Properties properties = new Properties();
properties.load(in);
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
Class.forName(driver);//驱动只要启动一次
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);//获取连接（数据库）
}
//释放资源
public static void release(Connection connection , Statement statement , ResultSet resultSet){
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (connection!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
复制代码然后进行增删改查
import utils.JdbcUtils;
import java.sql.*;
public class jdbctest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "SELECT `subjectno`,`subjectname` FROM `subject` WHERE `subjectno` <10";
resultSet = statement.executeQuery(sql);//获取结果
System.out.println("subjectno" + " subjectname");
while (resultSet.next()) {
System.out.println(" " + resultSet.getObject("subjectno") + " " + resultSet.getObject("subjectname"));
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
复制代码防止SQL注入
使用PreparedStatement
- 在sql语句中用 ？ 表示变量
import utils.JdbcUtils;
import java.sql.*;
public class jdbctest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement statement = null;
//ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "INSERT INTO subject (`subjectno`,`subjectname`,`classhour`,`gradeid`) VALUES(?,?,?,?)";
statement = connection.prepareStatement(sql);//预编译SQL，不执行
statement.setInt(1,19);//第一个？
statement.setString(2,"物理");//第二个？
statement.setInt(3,700);
statement.setInt(4,1);
int row = statement.executeUpdate();
if (row >0)
{
System.out.println("插入成功");
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally {
JdbcUtils.release(connection,statement,null);
}
}
}
复制代码给第几个？赋值就用
preparedStatement.setObject(?的位置,值),从1开始