JavaWeb 第12章 JDBC详解(三)
来源：互联网 发布：python自学视频 编辑：程序博客网 时间：2024/06/05 04:23
jdbc基本操作 CRUD
1.查询数据库
注册mysql驱动,获取Connecttion,创建Statement,查询数据库返回ResultSet,遍历ResultSet,关闭ResultSet,,关闭Statement,关闭Connection2.插入人员信息
增删改执行executeUpdate()查询执行 executeQuery()<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><!-- 12.21 12.3JDBC的基本操作 包括创建,查询,更新,修改简称CRUD --><%@ page import="java.sql.DriverManager" %><%@ page import="java.sql.Connection" %><%@ page import="java.sql.Statement" %><%@page import="java.sql.SQLException"%><%@page import="java.text.SimpleDateFormat"%><%@page import="java.sql.PreparedStatement"%><%@page import="java.sql.ResultSet"%><%@page import="java.sql.Date" %><%! public String forSQL(String sql){ return sql.replace("'", "\\'");//把单引号转换为\'' }%><% request.setCharacterEncoding("UTF-8");//设置编码 String name=request.getParameter("name");//获取name参数 String englishName=request.getParameter("englishName"); String age=request.getParameter("age"); String birthday=request.getParameter("birthday"); String sex=request.getParameter("sex"); String description=request.getParameter("description"); String action=request.getParameter("action"); //插入人员信息 if("add".equals(action)){ String sql="insert into tb_person"+ "(name,english_name,age,sex,birthday,description)"+ "values"+ "('"+forSQL(name)+"','"+forSQL(englishName)+"','"+age+"','"+sex+"','"+birthday+"','"+forSQL(description)+"')"; Connection conn=null; Statement stmt=null; int result=0; try{ DriverManager.registerDriver(new com.mysql.jdbc.Driver()); conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8","root","1233456"); stmt=conn.createStatement(); result=stmt.executeUpdate(sql); }catch(SQLException e){ out.println("执行sql"+sql+"时候发生了异常"+e.getMessage()); return; }finally{ if(stmt!=null){stmt.close();}; if(conn!=null){conn.close();}; } out.println("<html><style>body{font-size:12px;line-height:25px;}</style><body>"); out.println(result+"条记录被添加到数据库中"); out.println("<a href='listPerson.jsp'>返回人员列表</a>"); out.println("<br/></br/>执行的sql语句为:<br/>"+sql); return;3.注册数据库驱动
<!-- 注册数据库驱动的三种方式方式 --><% Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动 String url1="jdbc:mysql://localhost:3306/databasename";//数据库连接子协议 Connection conn1=DriverManager.getConnection(url1,"username","password"); //此方式由于参数为字符串，因此很容易修改，移植性强。 ------------------------------------------------------- new com.mysql.jdbc.Driver();//创建driver对象，加载数据库驱动 String url2="jdbc:mysql://localhost:3306/databasename";//数据库连接子协议 Connection conn2=DriverManager.getConnection(url2,"username","password"); //这里不需要这样写DriverManager.registerDriver(new com.mysql.jdbc.Driver()), //原因是com.mysql.jdbc.Driver类的静态代码快里面已经进行了修改的操作 //static { // java.sql.DriverManager.registerDriver(new Driver()); //} //由new com.mysql.jdbc.Driver()可以知道，这里需要创建一个类的实例。%>-----------------------------------------------方式三System.setProperty("jdbc.driver","com.mysql.jdbc.Driver");//系统属性指定数据库驱动 String url="jdbc:mysql://localhost:3306/databasename";//数据库连接子协议 Connection conn=DriverManager.getConnection(url,"username","password"); 4.获取自动插入的id
import java.sql.*;import com.mysql.jdbc.Driver;//获取自动插入的idpublic class GetGeneratedKeysTest { public static void main(String[] args) throws SQLException{ new Driver();//注册驱动 Connection conn=null; Statement stmt=null; ResultSet rs=null; try{ conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");//获取连接 stmt=conn.createStatement();//获取statement stmt.executeUpdate("insert into tb_person" +"(name,english_name,age,sex,birthday,decription)" +"values('Name','English_Name','17','男',current_date(),'')"); rs=stmt.getGeneratedKeys();//获取自动生成的键值 rs.next();//滚动到下一条 System.out.println("id"+rs.getInt(1));//输出第一列 }finally { if(rs!=null){rs.close();} if(stmt!=null){stmt.close();} if(conn!=null){conn.close();} } }}5.删除人员信息
//删除人员信息 }else if("del".equals(action)){ String[] id=request.getParameterValues("id");//获取一个或者多个id if(id==null||id.length==0){ out.println("没有选择任何行"); return; } String condition=""; for(int i=0;i<id.length;i++){ if(i==0){ condition=""+id[i]; }else{ condition=","+id[i]; } } String sql="Delete from tb_person where id in ("+condition+")"; Connection conn=null; Statement stmt=null; try{ DriverManager.registerDriver(new com.mysql.jdbc.Driver()); conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8","root","1233456"); stmt=conn.createStatement(); int result=stmt.executeUpdate(sql); out.println("<html><style>body{font-size:12px;line-height:25px;}</style><body>"); out.println(result+"条记录被添加到数据库中"); out.println("<a href='listPerson.jsp'>返回人员列表</a>"); out.println("<br/></br/>执行的sql语句为:<br/>"+sql); }catch(SQLException e){ out.println("执行sql"+sql+"时候发生了异常"+e.getMessage()); return; }finally{ if(stmt!=null){stmt.close();}; if(conn!=null){conn.close();}; }6.修改人员信息
//修改人员信息 }else if("edit".equals(action)){ String id=request.getParameter("id"); String sql="select * form tb_person where id="+id; Connection conn=null; Statement stmt=null; ResultSet rs=null; try{ DriverManager.registerDriver(new com.mysql.jdbc.Driver()); conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8","root","1233456"); stmt=conn.createStatement(); rs=stmt.executeQuery(sql); if(rs.next()){ request.setAttribute("id", rs.getString("id")); request.setAttribute("name", rs.getString("name")); request.setAttribute("englishName", rs.getString("englishName")); request.setAttribute("age", rs.getString("age")); request.setAttribute("sex", rs.getString("sex")); request.setAttribute("birthday", rs.getString("birthday")); request.setAttribute("decription", rs.getString("description")); request.setAttribute("action", action); request.getRequestDispatcher("addPerson.jsp").forward(request, response);//重定向到修改页面 }else{ out.println("没有找到id为"+id+"的记录"); } }catch(SQLException e){ out.println("执行sql"+sql+"时候发生了异常"+e.getMessage()); e.printStackTrace(); }finally{ if(rs!=null){rs.close();} if(stmt!=null){stmt.close();} if(conn!=null){conn.close();} } ---------------------------------------------增加人员信息<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><% String action=(String)request.getAttribute("action"); String id=(String)request.getAttribute("id"); String name=(String)request.getAttribute("name"); String englishName=(String)request.getAttribute("englishName"); String age=(String)request.getAttribute("age"); String sex=(String)request.getAttribute("sex"); String birthday=(String)request.getAttribute("id"); String description=(String)request.getAttribute("id"); boolean isEdit="edit".equals(action); %><html> <head> <meta http-equiv="Content-type" content="text/html;charset=UTF-8"> <title><%=isEdit?"修改人员资料":"新建人员资料" %></title> <style type="text/css">body, td{font-size:12px;}</style> </head> <body> <script type="text/javascript" src="js/calendar.js"></script>//日期时间控件 <form action="operatePerson.jsp" method="post"> <input type="hidden" name="action" value="<%=isEdit?"save":"add" %>">; <input type="hidden" name="id" value="<%=isEdit?id:"" %>"">; <fieldset> <legend><%=isEdit?"修改人员资料":"新建人员资料" %></legend> <table align=center> <tr> <td>姓名</td> <td><input type="text" name="name" value="<%=isEdit?name:"" %>"></td> </tr> <tr> <td>英文名</td> <td><input type="text" name="name" value="<%=isEdit?englishName:"" %>"></td> </tr> <tr> <td>性别</td> <td> <input type="radio" name="sex" value="男" id="sex_male" <%=isEdit&&"男".equals(sex)?"checked":"" %>> <label for="sex_male">男</label> <input type="radio" name="sex" value="女" id="sex_female"<%=isEdit&&"女".equals(sex)?"checked":"" %>> <label for="sex_female">女</label> </td> </tr> <tr> <td>年龄</td> <td><input type="text" name="age" value="<%=isEdit?age:"" %>"></td> </tr> <tr> <td>生日</td> <td> <input type="text" name="birthday" onfocus="setday(birthday)"value="<%=isEdit?birthday:"" %>"> <img src="images/calendar.gif" onclick="setday(birthday);"/>; </td> </tr> <tr> <td>描述</td> <td><textarea name="description" ><%=isEdit?description:"" %></textarea></td> </tr> <tr> <td></td> <td><input type="submit" value="<%=isEdit?"保存":"添加人员信息" %>"></td> </tr> <tr> <td>姓名</td> <td><input type="text" name="name" value="<%=isEdit?name:"" %>"></td> </tr> </table> </fieldset> </form> </body></html>-------------------------------------保存操作 //保存信息 }else if("save".equals(action)){ String id=request.getParameter("id"); String sql="update tb_person set"+ "name='"+forSQL(name)+"',"+ "english_name='"+forSQL(englishName)+"',"+ "sex='"+sex+"',"+ "age='"+age+"',"+ "brithday='"+birthday+"',"+ "description='"+forSQL(description)+"'"+ "where id="+id; Connection conn=null; Statement stmt=null; try{ DriverManager.registerDriver(new com.mysql.jdbc.Driver()); conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8","root","1233456"); stmt=conn.createStatement(); int result=stmt.executeUpdate(sql); if(result==0){ out.println("影响数目为0,修改失败."); }else{ out.println(result+"条记录被修改"); } out.println("<a href='listPerson.jsp'>返回人员列表</a>"); out.println("<br/><br/>执行的sql语句为:<br/>"+sql); }catch(SQLException e){ out.println("执行sql"+sql+"时候发生了异常"+e.getMessage()); }finally{ if(stmt!=null){stmt.close();} if(conn!=null){conn.close();} } }%>7.使用preparedStatement来保存
<!-- 使用preparestatement来保存数据 jdbc对于不变的部分进行预编译,下次直接执行,效率高--><% if("save".equals(action)){ String id=request.getParameter("id"); String sql="update tb_person set name=?,english_name=?,sex=?,age=?,birthday=?,description=? where id=?"; Connection conn=null; PreparedStatement preStmt=null; try{ DriverManager.registerDriver(new com.mysql.jdbc.Driver()); conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseWeb?characterEncoding=UTF-8","root","1233456"); SimpleDateFormat f=new SimpleDateFormat("yyyy-MM-dd"); Date d=new Date(f.parse(birthday).getTime()); preStmt=conn.prepareStatement(sql); preStmt.setString(1, name); preStmt.setString(2, englishName); preStmt.setString(3, sex); preStmt.setInt(4, Integer.parseInt(age)); preStmt.setDate(5, d); preStmt.setString(6, description); preStmt.setInt(7, Integer.parseInt(id)); int result=preStmt.executeUpdate(sql); if(result==0){ out.println("影响数目为0,修改失败."); }else{ out.println(result+"条记录被修改"); } out.println("<a href='listPerson.jsp'>返回人员列表</a>"); out.println("<br/><br/>执行的sql语句为:<br/>"+sql); }catch(SQLException e){ out.println("执行sql"+sql+"时候发生了异常"+e.getMessage()); }finally{ if(preStmt!=null){preStmt.close();} if(conn!=null){conn.close();} } }%>8.statement批量处理sql语句
import java.sql.*;import com.mysql.jdbc.Driver;//对比statement与preparedStatement的批量处理sql语句public class BatchTest { public static void main(String[] args) throws SQLException { new Driver(); Connection conn=null; Statement stmt=null; try { conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");//获取连接 stmt=conn.createStatement(); for (int i = 0; i < args.length; i++) { String sql="insert into tb_person" +"(name,english_name,age,sex,birthday,description)" +"values('Name'"+i+",'English_Name'"+i+",'17','男',current_date(),'')"; stmt.addBatch(sql); } int[] result=stmt.executeBatch(); System.out.println("影响的行数分别为"); for(int i=0;i<result.length;i++){ System.out.println(result[i]+","); } }finally{ if(stmt!=null){stmt.close();} if(conn!=null){conn.close();} } }}9.preparedstatement批量处理sql语句
import java.sql.*;import com.mysql.jdbc.Driver;public class preparedBatchTest { public static void main(String[] args) throws SQLException { new Driver(); Connection conn=null; PreparedStatement preStmt=null; try { conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");//获取连接 preStmt=conn.prepareStatement("insert into tb_person" +"(name,english_name,age,sex,birthday,description)" +"values(?,?,?,?,?)"); for(int i=0;i<5;i++){ int index=1; preStmt.setString(index++, "Name"+i); preStmt.setString(index++, "English Name"+i); preStmt.setInt(index++, 25); preStmt.setString(index++, "男"); preStmt.setDate(index++, new java.sql.Date(System.currentTimeMillis())); preStmt.setString(index++, ""); preStmt.addBatch(); } int[] result=preStmt.executeBatch(); System.out.println("影响的行数为"); for (int i = 0; i < result.length; i++) { System.out.println(result[i]+","); } }finally{ if(preStmt!=null){preStmt.close();} if(conn!=null){conn.close();} } }} 0 0
- JavaWeb 第12章 JDBC详解(三)
- JavaWeb 第12章 JDBC详解(一)
- JavaWeb 第12章 JDBC详解(二)
- JavaWeb 第12章 JDBC详解(五)高级应用
- javaweb开发学习JDBC应用三
- JDBC详解三
- jdbc详解（三）
- jdbc详解（三）
- JavaWeb-JDBC
- JavaWeb----JDBC
- JavaWeb:JDBC
- JavaWeb三大框架之---struts2详解
- JavaWeb开发第8章标准标签库(JSTL)之三国际化标签
- JDBC 详解（干货分享）（三）
- JavaWeb(三)
- 【JavaWeb】【笔记】《JavaWeb入门经典》 第15章 Struts框架
- JavaWeb JDBC驱动链接
- JavaWeb-15 (JDBC编程)
- phpcms首页输出组图
- android 把后台返回的数据中的逗号替换成空格
- Behavior Designer中文教程
- hdu 3303 Harmony Forever
- 常见的存储引擎
- JavaWeb 第12章 JDBC详解(三)
- Java运行时动态生成class的方法
- 从关注接口和父类（抽象类）的角度来提升阅读代码效率：
- maven的Module化的背景和方法：
- Runtime基本知识点以及应用场景
- AlexNet学习笔记-论文翻译和理解（二）
- 论转换器是定义成接口，然后用具体类实现，还是定义成一个静态方法：
- hadoop环境配置过程中可能遇到问题的解决方案
- 认识java中的Queue:
