JDBC中的PreparedStatement
2018-12-06 07:34:03来源:博客园 阅读 ()
使用Statement需要进行拼写SQL语句,很麻烦而且容易出错,这就用到了PreparedStatement。PreparedStatement是Statement的子接口,可以传入带占位符的SQL语句,并且提供了补充占位符变量的方法。
1.使用PreparedStatement
1.1 创建PreparedStatement;
String sql="INSERT INTO EXAMSTUDENT VALUES(?,?,?,?,?,?,?)"; PreparedStatement ps=conn.prepareStatement(sql);
1.2 调动PreparedStatement的setXxx(int index,Object val)设置占位符的值;
1.3 执行 SQL语句:executeQuery()或executeUpdate()。注意:执行时不再需要传入SQL语句。
示例代码:
@Test public void testPreparedStatement(){ Connection connection=null; PreparedStatement preparedstatement=null; try{ String sql="INSERT INTO EXAMSTUDENT VALUES(?,?,?,?,?,?,?)"; connection=JDBCTools.getConnection(); preparedstatement=connection.prepareStatement(sql); preparedstatement.setInt(1, 3); preparedstatement.setInt(2, 434); preparedstatement.setString(3, "198312"); preparedstatement.setString(4, "342"); preparedstatement.setString(5, "Peter"); preparedstatement.setString(6, "上海"); preparedstatement.setInt(7, 332); preparedstatement.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ JDBCTools.release(preparedstatement,connection); } }
2.使用PreparedStatement向数据表中添加学生信息
Student.java
package com.test.jdbc; public class Student { private int flowId; private int type; private String idCard; private String examCard; private String studentName; private String location; private int grade; public int getFlowId() { return flowId; } public void setFlowId(int flowId) { this.flowId = flowId; } public int getType() { return type; } public void setType(int type) { this.type = type; } public String getIdCard() { return idCard; } public void setIdCard(String idCard) { this.idCard = idCard; } public String getExamCard() { return examCard; } public void setExamCard(String examCard) { this.examCard = examCard; } public String getStudentName() { return studentName; } public void setStudentName(String studentName) { this.studentName = studentName; } public String getLocation() { return location; } public void setLocation(String location) { this.location = location; } public int getGrade() { return grade; } public void setGrade(int grade) { this.grade = grade; } public Student(int flowId, int type, String idCard, String examCard, String studentName, String location, int grade) { super(); this.flowId = flowId; this.type = type; this.idCard = idCard; this.examCard = examCard; this.studentName = studentName; this.location = location; this.grade = grade; } public Student(){ } @Override public String toString() { return "student [flowId=" + flowId + ", type=" + type + ", idCard=" + idCard + ", examCard=" + examCard + ", studentName=" + studentName + ", location=" + location + ", grade=" + grade + "]"; } }
工具类JDBCTools.java
package com.test.jdbc; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import org.junit.Test; public class JDBCTools { //添加数据并更新 public static void update2(String sql,Object...args){ Connection connection=null; PreparedStatement preparedstatement=null; try{ connection=JDBCTools.getConnection(); preparedstatement=connection.prepareStatement(sql); for(int i=0;i<args.length;i++){ preparedstatement.setObject(i+1,args[i]); } preparedstatement.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ JDBCTools.release(preparedstatement, connection); } } //获取数据库的连接 public static Connection getConnection() throws Exception{ String driverClass=null; String jdbcUrl=null; String user=null; String password=null; InputStream in=JDBCTools.class.getResourceAsStream("/jdbc.properties"); Properties properties=new Properties(); properties.load(in); driverClass=properties.getProperty("driver"); jdbcUrl=properties.getProperty("jdbcUrl"); user=properties.getProperty("user"); password=properties.getProperty("password"); Class.forName(driverClass); Connection connection=DriverManager.getConnection(jdbcUrl,user,password); return connection; } @Test public void testGetConnection() throws Exception{ getConnection(); } //数据库释放 public static void release(Statement statement,Connection connection){ if(statement!=null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
功能实现类:JDBCTest.java
package com.test.jdbc; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.Scanner; import org.junit.Test; public class JDBCTest { @Test public void testAddNewStudent2(){ Student student=getStudentFromConsole(); addNewStudent2(student); } //从控制台输入学生的信息 private Student getStudentFromConsole() { Scanner scanner=new Scanner(System.in); Student student=new Student(); System.out.print("FlowId:"); student.setFlowId(scanner.nextInt()); System.out.print("Type:"); student.setType(scanner.nextInt()); System.out.print("IDCard:"); student.setIdCard(scanner.next()); System.out.print("ExamCard:"); student.setExamCard(scanner.next()); System.out.print("StudentName:"); student.setStudentName(scanner.next()); System.out.print("Location:"); student.setLocation(scanner.next()); System.out.print("Grade:"); student.setGrade(scanner.nextInt()); return student; } public void addNewStudent2(Student student){ String sql="INSERT INTO EXAMSTUDENT VALUES(?,?,?,?,?,?,?)"; JDBCTools.update2(sql, student.getFlowId(),student.getType(),student.getIdCard(), student.getExamCard(),student.getStudentName(),student.getLocation(),student.getGrade()); } }
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- MyBatis中的$和#,用不好,准备走人! 2020-06-11
- SpringBoot 2.3 整合最新版 ShardingJdbc + Druid + MyBatis 2020-06-11
- 构造函数中的this()和super() 2020-06-10
- 怎么用Java 高效提取、替换、删除PDF文档中的图片 2020-06-09
- JSP+Structs+JDBC+mysql实现的诚欣电子商城 2020-06-08
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash