• jbdc总结


    1.使用纯java方式连接数据库

    2.使用Statement添加信息

    package com.sql;
    
    /**
     * 使用Statement添加宠物
     */
    import java.sql.*;
    
    public class Demo1 {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement ps = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {// 加载驱动
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.out.println("建立连接失败");
            }
    
            try {// 创建连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hello", "root", "123456");
                stmt = conn.createStatement();// 创建sql操作对象
                String name = "佩奇";
                int health = 40;
                int love = 90;
                String strain = "哈士奇";
    
                StringBuffer sbSql = new StringBuffer("insert into dog (name,health,love,strain) values ( '");
                sbSql.append(name + "',");
                sbSql.append(health + ",");
                sbSql.append(love + ",'");
                sbSql.append(strain + "')");
                // 打印拼接后的sql
                System.out.println(sbSql.toString());
    
                stmt.execute(sbSql.toString());
                // System.out.println("插入成功");
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("建立连接失败");
            } finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
    
            }
    
        }
    }
    View Code

    3.使用Statement更新信息

    package com.sql;
    
    /**
     * 使用Statement更新宠物
     */
    import java.sql.*;
    
    public class Demo1 {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement ps = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {// 加载驱动
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.out.println("建立连接失败");
            }
    
            try {// 创建连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hello", "root", "123456");
                stmt = conn.createStatement();// 创建sql操作对象
                String sql = "update dog set health=140,love=100 where name='佩奇'";
    
                int t = stmt.executeUpdate(sql);
                if (t > 0)
                    System.out.println("修改成功");
    
                // System.out.println("插入成功");
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("建立连接失败");
            } finally {
                try {
                    conn.close();
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
    
            }
    
        }
    }
    View Code

    4.用·Statement查询信息

    package com.sql;
    
    /**
     * 使用Statement查询宠物
     */
    import java.sql.*;
    
    public class Demo1 {
        public static void main(String[] args) {
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
    
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hello", "root", "123456");
                stmt = conn.createStatement();
                rs = stmt.executeQuery("select * from dog");
                while (rs.next()) {
                    System.out.print(rs.getString(1) + "	");
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    rs.close();// 结果集关闭
                    stmt.close();// 语句关闭
                    conn.close();// 连接关闭
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
        }
    }
    View Code

     5.用prepaedStatement防止SQL注入

    package com.sql;
    
    /**
     * 消除SQL注入隐患
     */
    import java.sql.*;
    import java.util.*;
    
    public class Demo1 {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hello", "root", "123456");
    
                System.out.println("*************************");
                System.out.println("        宠物主人                                ");
                System.out.println("*************************");
                Scanner input = new Scanner(System.in);
                System.out.println("输入姓名");
                String name = input.next();
                System.out.println("输入密码");
                String pass = input.next();
                String sql = "select* from master where name=? and password=?";
                ps = conn.prepareStatement(sql);
                ps.setString(1, name);
                ps.setString(2, pass);
                System.out.println(sql);
                // 结果
                rs = ps.executeQuery();
                if (rs.next()) {
                    System.out.println(rs.getString(1) + " " + rs.getString(2));
                } else {
                    System.out.println("登陆失败");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    rs.close();
                    ps.close();
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
    
            }
        }
    }
    View Code

     可以处理多条数据:

    package com.sql;
    
    import java.sql.*;
    import java.util.*;
    
    /**
     * @author Administrator 演示示例5:SQL注入隐患
     */
    public class Demo1 {
    
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
    
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");
    
                System.out.println("*************************");
                System.out.println("        宠物主人                                ");
                System.out.println("*************************");
    
                Map<String, String> paraMap = new HashMap<String, String>();
                paraMap.put("name", "王志");
                paraMap.put("password", "123456");
    
                String name = paraMap.get("name");
                String password = paraMap.get("password");
    
                String sql = "select * from master where 1=1 ";
    
                List<String> paraList = new ArrayList<String>();
                if (name != null && name.length() > 0) {
                    sql += " and name = ? ";
                    paraList.add(name);
                } // end of if
    
                if (password != null && password.length() > 0) {
                    sql += " and password = ? ";
                    paraList.add(password);
                } // end of if
    
                System.out.println(sql);
    
                pstmt = conn.prepareStatement(sql);
    
                for (int i = 0; i < paraList.size(); i++) {// 逐条演绎
                    System.out.println(paraList.get(i));
                    pstmt.setObject(i + 1, paraList.get(i));
                }
    
                // 注意:使用预编译语句不需要传入sql
                rs = pstmt.executeQuery();
    
                if (rs.next()) {
                    // System.out.println(rs.getString(2));
                    System.out.print("登录成功,欢迎您");
                } else {
                    System.out.print("登录失败");
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    rs.close();
                    pstmt.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
        }
    }
    View Code

    //标准版逐条演绎

    package com.sql;
    
    import java.sql.*;
    import java.util.*;
    
    /**
     * @author Administrator 演示示例5:SQL注入隐患
     */
    public class Demo1 {
    
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
    
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");
    
                System.out.println("*************************");
                System.out.println("        宠物主人                                ");
                System.out.println("*************************");
    
                Map<String, String> paraMap = new HashMap<String, String>();//容器
                paraMap.put("name", "王志");
                paraMap.put("password", "123456");
    
                paraMap.put("name", "王志");
                paraMap.put("password", "123456");
                for(int nn=0;nn<paraMap.size();nn++){
                String name = paraMap.get("name");//获取name
                String password = paraMap.get("password");//获取password
    
                String sql = "select * from master where 1=1 ";
    
                List<String> paraList = new ArrayList<String>();//String容器 存储sql语句
                if (name != null && name.length() > 0) {
                    sql += " and name = ? ";
                    paraList.add(name);
                } // end of if
    
                if (password != null && password.length() > 0) {
                    sql += " and password = ? ";
                    paraList.add(password);
                } // end of if
    
                System.out.println(sql);
    
                pstmt = conn.prepareStatement(sql);
    
                for (int i = 0; i < paraList.size(); i++) {// 逐条演绎
                    System.out.println(paraList.get(i));
                    pstmt.setObject(i + 1, paraList.get(i));
                }
    
                // 注意:使用预编译语句不需要传入sql
                rs = pstmt.executeQuery();
    
                if (rs.next()) {
                    // System.out.println(rs.getString(2));
                    System.out.print("登录成功,欢迎您");
                } else {
                    System.out.print("登录失败");
                }
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    rs.close();
                    pstmt.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
        }
    }
    View Code

    6.使用PreparedStatement更新宠物 

    package com.sql;
    
    import java.sql.*;
    import java.util.*;
    
    /**
     * @author Administrator 演示示例5:SQL注入隐患
     */
    public class Demo1 {
    
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
    
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8", "root", "123456");
                // 更新狗狗的数据到数据库
                String sql = "update dog set health=?, love =?, strain=? where name='佩奇'";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, 80);
                ps.setInt(2, 100);
                ps.setString(3, "小狗小狗");
                int t = ps.executeUpdate();
                if (t > 0) {
                    System.out.println("成功");
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                    // rs.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
        }
    }
    View Code
  • 相关阅读:
    JSON 序列化类 南京酷得软件
    哈哈哈哈哈哈 找回记忆
    Presto
    (转)在Total Commander下使用SVN
    在ubuntu12.04,64位中安装lnmp一键包mysql的问题
    阿里云服务器上搭建php环境+redis
    在ubuntu12.04,64位中安装nginx+php+redis+mysql
    Redis篇:单线程I/O模型
    工具篇:apachehttpClient 和 jdk11HttpClient的使用
    技能篇:关于缓存数据的一致性探讨
  • 原文地址:https://www.cnblogs.com/helloworld2019/p/10806893.html
Copyright © 2020-2023  润新知