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();