数据查询:
/**
* 登录/查询
*
* @param user
* @param password
* @throws ClassNotFoundException
*/
public static void login(String user, String password) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
// 1、加载驱动//6.0以下数据库需要加载
// Class.forName("com.mysql.jdbc.Driver");
// 2、建立连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?serverTimezone=GMT&useSSL=false",
"root", "root");
// 3、操作数据库
// 1)预编译SQL格式
String sql = "SELECT * FROM student WHERE name=? AND password=?";
statement = connection.prepareStatement(sql);
// 2)给SQL中的占位符设置值
statement.setString(1, user); // 给语句中的第一个问号设置值
statement.setString(2, password); // 给语句中的第二个问号设置值
// 3)执行查询
resultSet = statement.executeQuery();
// 4)从resultset中获取结果
if (resultSet.next()) {
System.out.println("登录成功,当前登录用户:" + user + ",性别:" + resultSet.getString("sex"));
} else {
System.out.println("登录失败,用户名或密码错误!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4、关闭连接
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
数据新增:
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
// 连接数据库
try {
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/server?serverTimezone=GMT&useSSL=false", "root", "root");
// 新增数据的sql语句
String sql = "insert into user values(?,?,?,?) ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
preparedStatement.setString(3, name);
preparedStatement.setInt(4, age);
preparedStatement.executeUpdate();
//关闭连接没有写
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
数据修改
// // 操作数据库
// String sql = "UPDATE student SET sex=? WHERE name=?";
// PreparedStatement statement = connection.prepareStatement(sql);
// statement.setString(1, "男");
// statement.setString(2, "李四");
// statement.executeUpdate();
// System.out.println("修改成功!");
//
// // 关闭连接
// statement.close();
// connection.close();
数据删除:
// PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM student WHERE id=2");
// preparedStatement.executeUpdate();
// System.out.println("删除成功!");
//
// // 关闭连接
// preparedStatement.close();
// connection.close();