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(); } } } }
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(); } } } }
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(); } } } }
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(); } } } }
可以处理多条数据:
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(); } } } }
//标准版逐条演绎
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(); } } } }
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(); } } } }