jdbc操作mysql
一.两种连接方式
1.基础的数据库连接方式
//使用反射加载驱动并注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8";
String user = "root";
String password = "密码";
//获取连接
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "insert into studentinfo(name, age) values(\"男神\", 23)";
//执行sql语句
statement.execute(sql);
//关闭连接
statement.close();
connection.close();
2.搭配配置文件的数据库连接方式
相比上一种方式,使用配置文件更加灵活
//使用反射加载驱动并注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String url = properties.getProperty("Url");
String user = properties.getProperty("User");
String password = properties.getProperty("Password");
//获取连接
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "insert into studentinfo(name, age) values(\"渣男\", 90)";
//执行sql语句
statement.execute(sql);
//关闭连接
statement.close();
connection.close();
mysql.properties内容:
Url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8
User=root
Password=密码
二.查询并获取结果集
//使用反射加载驱动并注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String url = properties.getProperty("Url");
String user = properties.getProperty("User");
String password = properties.getProperty("Password");
//获取连接
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "select name, age from studentinfo";
//执行sql语句
ResultSet resultSet = statement.executeQuery(sql);
//循环遍历结果集
while(resultSet.next()){
String name = resultSet.getString(1); //获取得到的数据的第一列
int age = resultSet.getInt(2); //获取得到的数据的第二列
System.out.println("name : " + name + "\tage : " + age);
}
//关闭连接
resultSet.close();
statement.close();
connection.close();
三.使用PreparedStatement替代Statement
PreparedStatement相比Statement执行效率更高,而且解决了sql注入问题,因此建议使用PreparedStatement
1.增删改操作(executeUpdate)
//使用反射加载驱动并注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String url = properties.getProperty("Url");
String user = properties.getProperty("User");
String password = properties.getProperty("Password");
//获取连接
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql = "insert into studentinfo(name, age) values(?, ?)"; //参数使用?代替
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "王麻子"); //为第一个参数赋值
preparedStatement.setInt(2, 25); //为第二个参数赋值
//执行sql语句
int res = preparedStatement.executeUpdate();
System.out.println(res > 0 ? "执行成功" : "执行失败");
//关闭连接
preparedStatement.close();
connection.close();
2.查询操作(executeQuery)
//使用反射加载驱动并注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String url = properties.getProperty("Url");
String user = properties.getProperty("User");
String password = properties.getProperty("Password");
//获取连接
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "select id, name, age from studentinfo where id > ?"; //参数使用?代替
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 2); //为第一个参数赋值
//执行sql语句
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
int age = resultSet.getInt(3);
System.out.println("id : " + id + "\tname : " + name + "\tage : " + age);
}
//关闭连接
resultSet.close();
preparedStatement.close();
connection.close();