本文详细介绍了如何使用Java的JDBC技术连接MySQL数据库，包括创建连接、执行SQL（查询、插入、更新、删除）以及登录验证的示例。
JDBC就是java语言用来连接数据库的一种技术，使用java程序来操作数据库，实现数据的存储和处理。
1.创建MySQL数据库链接
String url = "jdbc:mysql://localhost:3306/2109b"; String username = "root"; String password = "123456"; String sql = "select * from user"; //1.加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection(url,username,password); //3.创建执行SQL语句的对象 Statement statement = connection.createStatement(); //4.执行查询语句，得到查询结果 ResultSet resultSet = statement.executeQuery(sql); //取出结果集，通过封装，存入到集合中 //产生集合对象，存放封装的User对象 List<User> list=new ArrayList(); //循环结果集 while (resultSet.next()){ //产生封装的User对象 User user = new User(); //将查询出的每一个字段的记录存入封装对象User user.setId(resultSet.getInt("id")); user.setUsername(resultSet.getString("username")); user.setPassword(resultSet.getString("password")); user.setNickname(resultSet.getString("nickname")); //将对象存入到集合 list.add(user); } //循环结束 //迭代器集合，查看结果 for (User u:list){ System.out.println(u); }2.实现增加功能
//增加操作 @Test public void addUser() throws ClassNotFoundException, SQLException { String driver="com.mysql.cj.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/2109b"; String username = "root"; String password = "123456"; User user = new User(); user.setUsername("zhaoliu"); user.setPassword("zhaoliu123"); user.setNickname("赵六"); String sql = "insert into user(username,password,nickname)values('"+user.getUsername() +"','"+user.getPassword()+"','"+user.getNickname()+"')"; System.out.println(sql); //1.加载驱动 Class.forName(driver); //2.创建连接 Connection connection = DriverManager.getConnection(url,username,password); //3.创建执行SQL语句的对象 Statement statement = connection.createStatement(); //4.执行增加SQL int i = statement.executeUpdate(sql); //5.关闭资源 statement.close(); connection.close(); }3.实现删除功能
//删除操作 @Test public void deleteUser() throws ClassNotFoundException, SQLException { String driver = "com.mysql.cj.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/2109b"; String username = "root"; String password = "123456"; int id = 3; String sql = "delete from user where id="+id; //1.加载驱动 Class.forName(driver); //2.创建连接 Connection connection = DriverManager.getConnection(url,username,password); //3.创建执行SQL语句的对象 Statement statement = connection.createStatement(); //4.执行增加SQL int i = statement.executeUpdate(sql); //5.关闭资源 statement.close(); connection.close(); }4.实现修改功能
//修改操作 @Test public void updateUser() throws ClassNotFoundException, SQLException { String driver = "com.mysql.cj.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/2109b"; String username = "root"; String password = "123456"; User user = new User(); user.setUsername("ww"); user.setPassword("123456"); user.setNickname("需要"); user.setId(4); String sql = "update user set username='"+user.getUsername() +"',password='"+user.getPassword()+"',nickname='" +user.getNickname()+"'where id="+user.getId(); //1.加载驱动，注册驱动 Class.forName(driver); //2.创建连接 Connection connection = DriverManager.getConnection(url,username,password); //3.创建执行SQL语句的对象 Statement statement = connection.createStatement(); //4.执行修改的SQL int i = statement.executeUpdate(sql); //5.关闭资源 statement.close(); connection.close(); }5.实现查询功能
//通过id查询 @Test public void findByIdUser() throws ClassNotFoundException,SQLException{ String driver = "com.mysql.cj.jdbc.Driver"; String url ="jdbc:mysql://localhost:3306/2109b"; String username = "root"; String password = "123456"; int id = 1; String sql = "select * from user where id="+id; System.out.println(sql); //1.加载驱动，注册驱动 Class.forName(driver); //2.创建连接 Connection connection = DriverManager.getConnection(url,username,password); //3.执行查询SQl，得到结果集 Statement statement = connection.createStatement(); //4.执行查询SQl。得到结果集 ResultSet resultSet = statement.executeQuery(sql); //5.将结果集存入User对象，展示 User user = new User(); while (resultSet.next()){ user.setId(resultSet.getInt("id")); user.setUsername(resultSet.getString("username")); user.setPassword(resultSet.getString("password")); user.setNickname(resultSet.getString("nickname")); } System.out.println(user); //6.关闭资源 resultSet.close(); statement.close(); connection.close(); }6.展示所有用户
//查询所有用户 @Test public void findSoUser() throws ClassNotFoundException,SQLException{ String driver = "com.mysql.cj.jdbc.Driver"; String url ="jdbc:mysql://localhost:3306/2109b"; String username = "root"; String password = "123456"; String sql = "select * from user"; System.out.println(sql); //1.加载驱动，注册驱动 Class.forName(driver); //2.创建；链接 Connection connection = DriverManager.getConnection(url,username,password); //3.创建执行SQl语句的对象 Statement statement = connection.createStatement(); //4.执行查询SQl，得到结果集 ResultSet resultSet= statement.executeQuery(sql); //5.将结果集存入到User对象，展示 User user= new User(); while (resultSet.next()){ user.setId(resultSet.getInt("id")); user.setUsername(resultSet.getString("username")); user.setPassword(resultSet.getString("password")); user.setNickname(resultSet.getString("nickname")); System.out.println(user); } //6.关闭资源 resultSet.close(); statement.close(); connection.close();7.测试登入功能
Scanner scanner = new Scanner(System.in); //第一步：输入用户名和密码 System.out.println("请输入用户名"); String username=scanner.next(); System.out.println("请输入密码"); String password = scanner.next(); //第二步：去数据库验证用户名和密码是否存在，存在就输出：登入成功，不存在就输出：登入失败 //1.用查询的SQL语句 String sql = "select * from user where username='"+username+"' and password='"+password+"'"; //2.使用工具类JdbcUtils来创建链接 Connection connection = JdbcUtils.getConnection(); //3.创建执行SQl的对象 Statement statement = connection.createStatement(); //4.执行SQl ResultSet resultSet = statement.executeQuery(sql); //5.结果集里面只要用记录，就表示登入成功，没有记录，就表示登入失败 if (resultSet.next()){ System.out.println("登入成功"); }else { System.out.println("登入失败"); } //关闭资源 JdbcUtils.close(resultSet,statement,connection);
扫一扫
5225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
