20121855倪杰实验三

上海电力学院Java EE课程实验题目: 实验三 Java数据库高级编程学号：20121855姓名：倪杰院系：计算机科学与技术学院专业年级：计算机科学与技术2012级2014 年11月5日1.1、调试上面实验指导部分一.4部分代码package s1;import java.sql.*;public class sad {public static void main(String[] args) throws Exception{String driver = "com.mysql.jdbc.Driver";Class.forName(driver);String url= "jdbc:mysql://127.0.0.1:3306/mydb";Connection con=DriverManager.getConnection(url,"root","1234");Statement cmd=con.createStatement();ResultSet rs=cmd.executeQuery("select * from customers");while(rs.next()){System.out.printf("%-10s%-30s%-10s\n",rs.getString(1),rs.getString(2),rs.getString(3));}con.close();}}2、调试上面实验指导部分“四、数据库分层设计”部分代码Customer.javapackage ss;public class Customer {private int cusid;private String cusname,cusphone;public Customer(){}public Customer(int cusid,String cusname,String cusphone){ this.cusid=cusid;this.cusname=cusname;this.cusphone=cusphone;}public int getCusid() {return cusid;}public void setCusid(int cusid) {this.cusid = cusid;}public String getCusname() {return cusname;}public void setCusname(String cusname) {this.cusname = cusname;}public String getCusphone() {return cusphone;}public void setCusphone(String cusphone) {this.cusphone = cusphone;}}Customerdao.javapackage ss;import java.sql.*;import java.util.*;public class CustomerDao {public ArrayList<Customer> allCustomers() {ArrayList<Customer> list = new ArrayList<Customer>();String sql = "select * from customers";ResultSet rs = DBhelper.executeQuery(sql);try{while (rs.next()) {Customer cus = new Customer();cus.setCusid(rs.getInt(1));cus.setCusname(rs.getString(2));cus.setCusphone(rs.getString(3));list.add(cus);}} catch (Exception ex) {ex.printStackTrace();}DBhelper.closeConnection();return list;}public void deleteCustomerByID(int id) {String sql = "delete from customers where customerid="+id;DBhelper. executeUpdate(sql);}public void addCustomer(Customer cus) {String sql = "insert into customers values("+cus.getCusid()+",'"+ cus.getCusname()+"','"+ cus.getCusphone()+"')";DBhelper. executeUpdate(sql);}}Dbhelper.javapackage ss;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class DBhelper {private static String driver = "com.mysql.jdbc.Driver";private static String url = "jdbc:mysql://127.0.0.1:3306/mydb";private static String user = "root", pwd = "1234";private static Connection con;/*加载驱动程序*/static {try {Class.forName(driver);} catch (Exception ex) {ex.printStackTrace();}}/*执行增删改操作*/public static void executeUpdate(String sql) {try {con = DriverManager.getConnection(url, user, pwd);Statement cmd = con.createStatement();cmd.executeUpdate(sql);con.close();} catch (Exception ex) {ex.printStackTrace();}}/*执行查询操作*/public static ResultSet executeQuery(String sql) {ResultSet rs = null;try {con = DriverManager.getConnection(url, user, pwd);Statement cmd = con.createStatement();rs = cmd.executeQuery(sql);} catch (Exception ex) {ex.printStackTrace();}return rs;}/*执行关闭数据库连接操作*/public static void closeConnection() {try {if(!con.isClosed())con.close();} catch (Exception ex) {}}}Demo.javapackage ss;import java.io.*;import java.util.*;public class Demo {public static void main(String[] args) throws Exception { CustomerDao dao = new CustomerDao();/*增加一个顾客*/Customer cus1=new Customer();cus1.setCusid(1011);cus1.setCusname("zhangsan");cus1.setCusphone("010-11111");dao.addCustomer(cus1);/*查询所有顾客*/ArrayList<Customer> list=dao.allCustomers();for(int i=0;i<list.size();i++){Customer cus=list.get(i);System.out.println(cus.getCusid()+" "+cus.getCusname()+" "+cus.getCusphone());}/*通过编号删除顾客*/dao.deleteCustomerByID(1010);}}3、运用上面数据库设计思想，继续完成上次实验。

以JDBC技术创建一个通讯录应用程序，要求通讯录中必须含有编号、姓名，性别、电话、地址、Email等等。

实现该类并包含添加、删除、修改、按姓名查等几个方法。

编写主程序测试。

Person.javapackage aa;public class Person {private int pid; //编号private String name; // 姓名private String sex; // 性别private String tel; // 电话private String address; // 地址private String email;public int getPid() {return pid;}public void setPid(int pid) {this.pid = pid;}public String getName() {return name;}public void setName(String name) { = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getTel() {return tel;}public void setTel(String tel) {this.tel = tel;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}}Dbhelper.javapackage aa;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class DBhelper {private static String driver = "com.mysql.jdbc.Driver";private static String url = "jdbc:mysql://127.0.0.1:3306/mydb";private static String user = "root", pwd = "1234";private static Connection con;/*加载驱动程序*/static {try {Class.forName(driver);} catch (Exception ex) {ex.printStackTrace();}}/*执行增删改操作*/public static void executeUpdate(String sql) {try {con = DriverManager.getConnection(url, user, pwd);Statement cmd = con.createStatement();cmd.executeUpdate(sql);con.close();} catch (Exception ex) {ex.printStackTrace();}}/*执行查询操作*/public static ResultSet executeQuery(String sql) { ResultSet rs = null;try {con = DriverManager.getConnection(url, user, pwd);Statement cmd = con.createStatement();rs = cmd.executeQuery(sql);} catch (Exception ex) {ex.printStackTrace();}return rs;}/*执行关闭数据库连接操作*/public static void closeConnection() {try {if(!con.isClosed())con.close();} catch (Exception ex) {}}}Persondao.javapackage aa;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;public class PersonDao {// 添加联系人public ArrayList<Person> allCustomers() {ArrayList<Person> list = new ArrayList<Person>();String sql = "select * from person";ResultSet rs = DBhelper.executeQuery(sql);try{while (rs.next()) {Person cus = new Person();cus.setPid(rs.getInt(1));cus.setName(rs.getString(2));cus.setSex(rs.getString(3));cus.setTel(rs.getString(4));cus.setAddress(rs.getString(5));cus.setEmail(rs.getString(6));list.add(cus);}} catch (Exception ex) {ex.printStackTrace();}DBhelper.closeConnection();return list;}public void deletePersonByID(int pid){String sql = "delete from person where personpid="+pid;DBhelper. executeUpdate(sql);}public void addPerson(Person cus) {String sql = "insert into person values("+cus.getPid()+",'"+cus.getName()+"','"+cus.getSex()+",'"+cus.getTel()+"','"+cus.getAddress()+"','"+cus.getEmail()+ "')";DBhelper. executeUpdate(sql);}public List<Person> queryPersonByName(String name){A rrayList<Person> list = new ArrayList<Person>();S tring sql="select * from persons where name='"+name+"'";ResultSet rs = DBhelper.executeQuery(sql);try{while (rs.next()) {Person p = new Person();p.setPid(rs.getInt(1));p.setName(rs.getString(2));p.setSex(rs.getString(3));p.setTel(rs.getString(4));p.setAddress(rs.getString(5));p.setEmail(rs.getString(6));list.add(p);}}catch (Exception ex) {ex.printStackTrace();}DBhelper.executeQuery(sql);DBhelper.closeConnection();return list;}}Demo.javapackage aa;import java.io.*;import java.util.*;public class Demo {public static void main(String[] args) throws Exception { PersonDao dao = new PersonDao();/*增加一个顾客*/Person cus1=new Person();cus1.setPid(1);cus1.setName("zhangsan");cus1.setTel("010-11111");cus1.setAddress("上海");cus1.setEmail("");cus1.setSex("男");dao.addPerson(cus1);dao.queryPersonByName("zhangsan");/*通过编号删除顾客*/dao.deletePersonByID(1010);}}4、在数据库中建立一个表，表名为学生，其结构为：学号、姓名、性别、年龄、成绩。