javaweb_JDBC

迈不过友情╰ 2022-01-12 06:51 271阅读 0赞

\# JDOB

> java database connectivity，sun公司提供的一套操作数据库的的标准规范，开发者可以利用jdbc在java中实现对数据库的增删改查，jdbc相当于java和数据库之间的一座桥梁

\# JDBC编写步骤

> 1：注册驱动（需拷入相关jar包）
> 
> 2：获取连接Connection
> 
> 3：得到执行sql语句的对象Statement
> 
> 4：执行sql语句，并返回结果
> 
> 5：处理结果
> 
> 6：关闭Connection
> 
> 代码示例：
> 
>     package my;
>     import java.sql.Connection;
>     import java.sql.DriverManager;
>     import java.sql.ResultSet;
>     import java.sql.Statement;
>     public class myjdbc {
>           
>         public static void main(String[] args) throws Exception {
>             // 1 注册驱动
>             Class.forName("com.mysql.jdbc.Driver");
>             // 2 获取连接Connection(mydatabase：数据库名称，root:登录数据库用户名，123456：密码)
>             Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root", "123456");
>             // 3 得到执行sql语句的对象Statement
>             Statement stmt = conn.createStatement();
>             // 4 执行sql语句，并返回结果（从表t_user中查找 id,name,password,email,birthday）
>             ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user");
>             // 5 处理结果
>             while(rs.next()){
>                 System.out.println(rs.getObject("id"));
>                 System.out.println(rs.getObject("name"));
>                 System.out.println(rs.getObject("password"));
>                 System.out.println(rs.getObject("email"));
>                 System.out.println(rs.getObject("birthday"));
>                 System.out.println("---------------------------");
>             }
>             // 6 关闭Connection
>             rs.close();
>             stmt.close();
>             conn.close();
>         }
>     }
> 
>  
> 
> \# 利用JDBC实现对数据库的增删改查
> 
> // 增加数据
> 
>     public void testInsert() throws Exception{
>         //注册驱动
>         Class.forName("com.mysql.jdbc.Driver");
>         
>         //获取连接Connection
>         Connection conn = DriverManager.getConnection("dbc:mysql://localhost:3306/mydatabase","root", "123456");
>         
>         //得到执行sql语句的对象statement
>         Statement stmt = conn.createStatement();
>         
>         //执行sql语句，并得到返回结果（当返回的结果大于0时，表示插入数据成功）
>         int flag = stmt.executeUpdate("insert into t_user(name,password,email,birthday) values ('cat','123456','cat@163.com','1993-11-11')");
>         if(flag > 0){
>             System.out.println("成功");
>         }
>         
>         //关闭资源
>         stmt.close();
>         conn.close();
>     }
> 
> // 更新数据
> 
>     public void testDelete() throws Exception{
>         //注册驱动
>         Class.forName("com.mysql.jdbc.Driver");
>         
>         //获取连接Connection
>         Connection conn = DriverManager.getConnection("dbc:mysql://localhost:3306/mydatabase","root", "123456");
>         
>         //得到执行sql语句的对象statement
>         Statement stmt = conn.createStatement();
>         
>         //执行sql语句，并得到返回结果
>         int flag = stmt.executeUpdate("delete from t_user where name='cat'");
>         if(flag > 0){
>             System.out.println("成功");
>         }    
>         //关闭资源
>         stmt.close();
>         conn.close();
>     }
> 
>  
> 
> \# 注：上方两个程序存在的问题：如果在"执行sql语句，并返回结果"时发生异常，程序就会停止，而前面所得资源还未关闭，所以可以将关闭资源得代码放在funally中，具体修改代码如下
> 
>     public static void main(String[] args) {
>             Connection conn = null;
>             Statement stmt = null;
>             ResultSet rs = null;
>             // 注册驱动
>             try {
>                 Class.forName("com.mysql.jdbc.Driver");
>                 // 获取连接Connection
>                 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root",
>                         "12456");
>                 // 得到执行sql语句的对象Statement
>                 stmt = conn.createStatement();
>                 // 执行sql语句，并返回结果
>                 rs = stmt.executeQuery("select id,name,password,email,birthday from t_user");
>                 // 处理结果
>                 while (rs.next()) {
>                     System.out.println(rs.getObject("id"));
>                     System.out.println(rs.getObject("name"));
>                     System.out.println(rs.getObject("password"));
>                     System.out.println(rs.getObject("email"));
>                     System.out.println(rs.getObject("birthday"));
>                     System.out.println("------------");
>                 }
>             } catch (ClassNotFoundException e) {
>                 e.printStackTrace();
>             } catch (SQLException e) {
>                 e.printStackTrace();
>             } finally {
>                 // 关闭Connection
>                 try {
>                     if(rs != null){
>                         rs.close();
>                     }
>                     rs = null;
>                     if(stmt != null){
>                         stmt.close();
>                     }
>                     stmt = null;
>                     if(conn != null){
>                         conn.close();
>                     }
>                     conn = null;
>                 } catch (SQLException e) {
>                     e.printStackTrace();
>                 }
>             }
>         }
>     }
> 
>  
> 
>  
> 
>  // 查看数据
> 
>     # 查找数据库文件
>     public void testSelect(){
>         Connection conn = null;
>         Statement stmt = null;
>         ResultSet rs = null;
>         try {
>             // 注册驱动
>             Class.forName("com.mysql.jdbc.Driver");
>             // 获取连接Connection
>             conn = DriverManager.getConnection("dbc:mysql://localhost:3306/mydatabase","root", "123456");
>             // 得到执行sql语句的对象Statement
>             stmt = conn.createStatement();
>             // 执行sql语句，并返回结果
>             rs = stmt.executeQuery("select id,name,password,email,birthday from t_user");
>             // 处理结果
>             List<User> userList = new ArrayList<>();
>             //User u = new User(); 如果将创建用户对象放于循环外，就指的是创建一个用户对象，这个用户对象被不断的赋值，
>     　　　　　　　　在List中添加的每一个数据对象，都是指向了同一个用户对象，它们的值都是最后一个添加的数据的值    
>             while (rs.next()) {
>                 User u = new User();// 每循环一次创建一个对象，后依据下方代码给对象赋值，后将被赋值的用户对象添加到userList中
>                 u.setId(rs.getInt("id"));
>                 u.setName(rs.getString("name"));
>                 u.setPassword(rs.getString("password"));
>                 u.setEmail(rs.getString("email"));
>                 u.setBirthday(rs.getDate("birthday"));
>                 userList.add(u);        // 将数据存储到List中
>             }
>             System.out.println(userList);
>         } catch (ClassNotFoundException e) {
>             e.printStackTrace();
>         } catch (SQLException e) {
>             e.printStackTrace();
>         } finally{
>             // 关闭Connection
>             try {
>                 if(rs != null){
>                     rs.close();
>                 }
>                 if(stmt != null){
>                     stmt.close();
>                 }
>                 if(conn != null){
>                     conn.close();
>                 }    
>             } catch (SQLException e) {
>                 e.printStackTrace();
>             }
>         }    
>     }
>     
>     // User.java文件
>     public class User {
>     
>         private int id;
>         private String name;
>         private String password;
>         private String email;
>         private Date birthday;
>         
>         public int getId() {
>             return id;
>         }
>         public void setId(int id) {
>             this.id = id;
>         }
>         public String getName() {
>             return name;
>         }
>         public void setName(String name) {
>             this.name = name;
>         }
>         public String getPassword() {
>             return password;
>         }
>         public void setPassword(String password) {
>             this.password = password;
>         }
>         public String getEmail() {
>             return email;
>         }
>         public void setEmail(String email) {
>             this.email = email;
>         }
>         public Date getBirthday() {
>             return birthday;
>         }
>         public void setBirthday(Date birthday) {
>             this.birthday = birthday;
>         }
>         @Override
>         public String toString() {
>             return "User [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + ",
>     　　 birthday="+ birthday + "]";
>         }
>     }
> 
> \# jdk7 和 JDBC4.1后正确关闭资源的方式
> 
>     @Test
>     public void testSelect() {
>         // 注册驱动
>         try {
>             Class.forName("com.mysql.jdbc.Driver");
>         } catch (ClassNotFoundException e) {
>             e.printStackTrace();
>         }
>         
>         // 获取连接Connection
>         try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root",
>                 "123456");
>                 // 得到执行sql语句的对象Statement
>                 Statement stmt = conn.createStatement();
>                 // 执行sql语句，并返回结果
>                 ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user")) {
>     
>             // 处理结果
>             List<User> userList = new ArrayList<>();
>             while (rs.next()) {
>                 User u = new User();
>                 u.setId(rs.getInt("id"));
>                 u.setName(rs.getString("name"));
>                 u.setPassword(rs.getString("password"));
>                 u.setEmail(rs.getString("email"));
>                 u.setBirthday(rs.getDate("birthday"));
>                 userList.add(u);
>             }
>             System.out.println(userList);
>         } catch (SQLException e) {
>             e.printStackTrace();
>         } 
>     }
>     }
> 
>  
> 
>  \# 在配置文件中存储JDBC相关配置信息
> 
>     // 新建配置文件(src下创建)db.properties
>         driverClass=com.mysql.jdbc.Driver
>         url=jdbc:mysql://localhost:3306/mydatabase
>         username=root
>         password=123456
>     
>     // 加载配置文件（创建一个工具类）public class DBUtil {
>             
>             private static String driverClass;
>             private static String url;
>             private static String username;
>             private static String password;
>             
>             static{
>                 ResourceBundle rb = ResourceBundle.getBundle("db");
>                 driverClass = rb.getString("driverClass");
>                 url = rb.getString("url");
>                 username = rb.getString("username");
>                 password = rb.getString("password");
>                 try {
>                     //注册驱动
>                     Class.forName(driverClass);
>                 } catch (ClassNotFoundException e) {
>                     e.printStackTrace();
>                 }
>             }    
>             public static Connection getConnection() throws SQLException{
>                 return DriverManager.getConnection(url, username, password);
>             }
>         }
> 
>  
> 
>  \# 改造后的查看结果程序
> 
>     public void testSelect() {
>         // 获取连接Connection
>         try (
>                 Connection conn = DBUtil.getConnection();
>                 // 得到执行sql语句的对象Statement
>                 Statement stmt = conn.createStatement();
>                 // 执行sql语句，并返回结果
>                 ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user")) {
>     
>             // 处理结果
>             List<User> userList = new ArrayList<>();
>             while (rs.next()) {
>                 User u = new User();
>                 u.setId(rs.getInt("id"));
>                 u.setName(rs.getString("name"));
>                 u.setPassword(rs.getString("password"));
>                 u.setEmail(rs.getString("email"));
>                 u.setBirthday(rs.getDate("birthday"));
>                 userList.add(u);
>             }
>             System.out.println(userList);
>         } catch (SQLException e) {
>             e.printStackTrace();
>         } 
>     }
> 
>  
> 
>  \# 模拟用户进行登录
> 
>     // 模拟登录
>     public class LoginService {
>         public User findUserByNameAndPassword(String name, String password) {
>             User u = null;
>             String sql = "select id,name,password,email,birthday from t_user where name='" + name + "' and password='" + password + "'";
>             System.out.println(sql);
>             // 获取连接Connection
>             try (Connection conn = DBUtil.getConnection();
>                     // 得到执行sql语句的对象Statement
>                     Statement stmt = conn.createStatement();
>                     // 执行sql语句，并返回结果
>                     ResultSet rs = stmt.executeQuery(sql)) {
>     
>                 // 处理结果
>                 while (rs.next()) {
>                     u = new User();
>                     u.setId(rs.getInt("id"));
>                     u.setName(rs.getString("name"));
>                     u.setPassword(rs.getString("password"));
>                     u.setEmail(rs.getString("email"));
>                     u.setBirthday(rs.getDate("birthday"));
>                 }
>             } catch (SQLException e) {
>                 e.printStackTrace();
>             }
>             return u;
>         }
>     }
>     
>     // 输入用户名和密码
>     public class Login {
>     
>         public static void main(String[] args) {
>             Scanner input = new Scanner(System.in);
>             System.out.println("请输入用户名：");
>             String name = input.nextLine();
>             System.out.println("请输入密码：");
>             String password = input.nextLine();
>             
>             //LoginService ls = new LoginService();
>             LoginServiceNew ls = new LoginServiceNew();
>             User u = ls.findUserByNameAndPassword(name, password);
>             if(u == null){
>                 System.out.println("用户名或密码错误!");
>             }else{
>                 System.out.println("登录成功!");
>             }
>         }
>     }
> 
>  
> 
> \# 模拟用户登录存在的问题 （sql注入问题）
> 
>     当登陆时输入
>     用户名
>     user1
>     密码
>     hello' or 1='1
>     
>     此时的sql语句为
>     "select id,name,password,email,birthday from t_user where name='user1' and password='hello' or 1='1'"
>     注意看后面的or 1='1'    // 当前面的用户名和密码对不上时为false，然而后面的1='1'的值都是true所以还是可以查出结果的
>     
>     
>     可以解决sql注入问题
>     PreparedStatement性能高于Statement（PreparedStatement有个预编译的效果）
>     
>     public class LoginServiceNew {
>         public User findUserByNameAndPassword(String name, String password) {
>             User u = null;
>             String sql = "select id,name,password,email,birthday from t_user where name=? and password=?";
>             System.out.println(sql);
>             // 获取连接Connection
>             try (Connection conn = DBUtil.getConnection();
>                      stmt = conn.prepareStatement(sql)) {
>                 stmt.setString(1, name);        // 相当于对第一个 "?"赋值
>                 stmt.setString(2, password);    // 相当于对第二个 "?"赋值
>     
>                 try (// 执行sql语句，并返回结果
>                         ResultSet rs = stmt.executeQuery()) {
>                     // 处理结果
>                     while (rs.next()) {
>                         u = new User();
>                         u.setId(rs.getInt("id"));
>                         u.setName(rs.getString("name"));
>                         u.setPassword(rs.getString("password"));
>                         u.setEmail(rs.getString("email"));
>                         u.setBirthday(rs.getDate("birthday"));
>                     }
>                 }
>             } catch (SQLException e) {
>                 e.printStackTrace();
>             }
>             return u;
>         }
>     }
> 
> \# 利用上方的方法修改插入数据
> 
>     @Test
>     public void testInsert() {
>         String sql = "insert into t_user(name,password,email,birthday) values (?,?,?,?)";
>         try(
>                 Connection conn = DBUtil.getConnection();
>                 PreparedStatement stmt = conn.prepareStatement(sql);
>                 ) {
>             stmt.setString(1, "dog");
>             stmt.setString(2, "123456");
>             stmt.setString(3, "dog@163.com");
>             //stmt.setDate(4, new java.sql.Date(new Date().getTime()));
>             stmt.setString(4, "1997-07-01");
>             int flag = stmt.executeUpdate();
>             if (flag > 0) {
>                 System.out.println("成功");
>             }else {
>                 System.out.println("插入失败");
>             }
>         } catch (SQLException e) {
>             e.printStackTrace();
>         }
>     }
> 
>

待补充...

转载于:https://www.cnblogs.com/Doaoao/p/10600758.html

javaweb_JDBC

发表评论取消回复

还没有评论，来说两句吧...

相关阅读