JavaEE-实验三 Java数据库高级编程

时间：2024-04-30 20:58:48

该博客仅专为我的小伙伴提供参考而附加，没空加上代码具体解析，望各位谅解

1、在MySQL中运行以下脚本

CREATE DATABASE mydatabase;

USE mydatabase;

CREATE TABLE customers (

customerID varchar(8) primary key,

name varchar(40) default NULL,

phone varchar(16) default NULL

);

INSERT INTO customers VALUES ('ADDIFK01','Frank Addinsell',

'(718) 555-3911');

INSERT INTO customers VALUES ('ALBIBB01','Bob Albinoni',

'(213) 555-7566');

 使用 JDBC编写程序读出数据库表中数据并在Console下如下输出内容

JavaEE-实验三 Java数据库高级编程

2、调试上面实验指导部分“四、数据库分层设计”部分代码

依据上课时教学内容补充SQLHelper类内容，为其增加

1） 执行带参数的SQL语句方法，含执行Select及CUID两个函数；

public static ResultSet executeQuery(String sql,Object[] params) {

        try {

            rs= getPreparedStatement(sql,params).executeQuery();

            return rs;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }

}

2） 为其增加返回唯一值得函数（如计算最大值、记录数等）；

    public static Object executeQueryScala(String sql) {

        Object o=null;

        try {

             rs=executeQuery(sql, null);

            if(rs.next()) {

                o=rs.getDate(1);

            }

            if(o!=null)

                return o;

            else

                return null;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }finally {

            DBUtil.closeAll(rs, pst, DBUtil.connection);

        }

    }

 3） 增加返回集合的函数

    public static  List<Object[]> executeQueryList(String sql){

        Object[] o=new Object[2];

        List<Object[]> obs=new ArrayList<Object[]>();

        PreparedStatement pst =null;

        ResultSet rs =null;

        try {

            rs = DBUtil.executeQuery(sql, null);

            while(rs.next()) {       /*未知列数 因此提取不出所有*/

                o[0]=(rs.getObject(0));

                o[1]=(rs.getObject(0));

                obs.add(o);

            }

            return obs;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }finally {

            DBUtil.closeAll(rs, pst, DBUtil.connection);

        }

    }

3、运用上面数据库设计思想，继续完成上次实验。以JDBC技术创建一个通讯录应用程序，要求通讯录中必须含有编号、姓名，性别、电话、地址、Email等等。实现该类并包含添加、删除、修改、按姓名查等几个方法。编写主程序测试。

JavaEE-实验三 Java数据库高级编程

该程序体量较小 没增加server层 （用于连接dao层与view层）

第一步：编写一个Person联系人类

package entity;

public class Person {

    private int    pid;  //编号

    private String name; // 姓名

    private String sex; // 性别

    private String tel; // 电话

    private String address; // 地址

    private String email; //email 

    public Person(int pid, String name, String sex, String tel, String address, String email) {

        this.pid = pid;

        this.name = name;

        this.sex = sex;

        this.tel = tel;

        this.address = address;

        this.email = email;

    }

    public int getPid() {

        return pid;

    }

    public void setPid(int pid) {

        this.pid = pid;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.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;

    }

    @Override

    public String toString() {

        return "Person [pid=" + pid + ", name=" + name + ", sex=" + sex + ", tel=" + tel + ", address=" + address

                + ", email=" + email + "]";

    }

}

第二步：编写一个PersonDao封装对联系人类的有关操作

package dao;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import entity.Person;

import util.DBUtil;

public class PersonDao {

    //增加人

    public void addPerson(Person p){

        String sql="insert into customers values(?,?,?,?,?,?);";

        Object params[]= {p.getPid(),p.getName(),p.getSex(),p.getTel(),p.getAddress(),p.getEmail()};

        DBUtil.executeUpdate(sql, params);

    }

    // 通过人的编号删除 联系人

    public void deletePersonByID(int pid){

        String sql="delete from person where pid=?";

        Object params[]= {pid};

        DBUtil.executeUpdate(sql,params);

    }

    //通过人的姓名查找联系人，返回一个集合

    public List<Person> queryPersonByName(String name){

        Person p=null;

        List<Person> ps=new ArrayList<Person>();

        PreparedStatement pst =null;

        ResultSet rs =null;

        try {

            String sql="select * from person where name=?";

            Object params[]= {name};

            rs = DBUtil.executeQuery(sql, params);

            while(rs.next()) {

                int id=rs.getInt("customerID");

                String sex=rs.getString("sex");

                String tel=rs.getString("tel");

                String address=rs.getString("address");

                String email=rs.getString("email");

                p=new Person(id, name, sex,tel,address,email);

                ps.add(p);

            }

            return ps;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }finally {

            DBUtil.closeAll(rs, pst, DBUtil.connection);

        }

    }

    //        查询所有人

    public List<Person> quaryall(){

        Person p=null;

        List<Person> ps=new ArrayList<Person>();

        PreparedStatement pst =null;

        ResultSet rs =null;

        try {

            String sql="select * from person";

            Object params[]= {};

            rs = DBUtil.executeQuery(sql, params);

            while(rs.next()) {

                int id=rs.getInt("pid");

                String name=rs.getString("name");

                String sex=rs.getString("sex");

                String tel=rs.getString("tel");

                String address=rs.getString("address");

                String email=rs.getString("email");

                p=new Person(id, name, sex,tel,address,email);

                ps.add(p);

            }

            return ps;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }finally {

            DBUtil.closeAll(rs, pst, DBUtil.connection);

        }

    }

    public List<Person> quarybyitems(String strsql) {

        Person p=null;

        List<Person> ps=new ArrayList<Person>();

        PreparedStatement pst =null;

        ResultSet rs =null;

        try {

            String sql="select * from person where 1=1 "+strsql;

            Object params[]= {};

            rs = DBUtil.executeQuery(sql, params);

            while(rs.next()) {

                int id=rs.getInt("pid");

                String name=rs.getString("name");

                String sex=rs.getString("sex");

                String tel=rs.getString("tel");

                String address=rs.getString("address");

                String email=rs.getString("email");

                p=new Person(id, name, sex,tel,address,email);

                ps.add(p);

            }

            return ps;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }finally {

            DBUtil.closeAll(rs, pst, DBUtil.connection);

        }

    }

    public boolean update(String strsql) {

        String sql="insert person values"+strsql;

        Object[] params= {};

        return DBUtil.executeUpdate(sql, params);

    }

}

第三步：编写一个测试Swing类,有界面使用JTable显示，参考书本上关于JTable控件的使用

演示截图

JavaEE-实验三 Java数据库高级编程

package view;

import java.awt.BorderLayout;

import java.awt.GridLayout;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.util.ArrayList;

import java.util.List;

import javax.swing.JButton;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JPanel;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;

import dao.PersonDao;

import entity.Person;

public class TestFrm extends JFrame{

    private static final long serialVersionUID = 1L;

    PersonDao pd=new PersonDao();

    JPanel jp1=new JPanel();

    Object[] Names1 = {"编号","姓名","性别","电话","地址","email"};

    JTable table1;

    TestFrm(){

        JLabel label1=new JLabel("编号");

        JLabel label2=new JLabel("姓名");

        JLabel label3=new JLabel("性别");

        JLabel label4=new JLabel("电话");

        JLabel label5=new JLabel("地址");

        JLabel label6=new JLabel("email");

        JTextField jt1=new JTextField();

        JTextField jt2=new JTextField();

        JTextField jt3=new JTextField();

        JTextField jt4=new JTextField();

        JTextField jt5=new JTextField();

        JTextField jt6=new JTextField();

        JButton jb1=new JButton("查询");

        JButton jb2=new JButton("增加");

        JButton jb3=new JButton("删除");

        JButton jb4=new JButton("显示所有");

        jp1.setLayout(new GridLayout(4, 4));

        jp1.add(label1);

        jp1.add(jt1);

        jp1.add(label2);

        jp1.add(jt2);

        jp1.add(label3);

        jp1.add(jt3);

        jp1.add(label4);

        jp1.add(jt4);

        jp1.add(label5);

        jp1.add(jt5);

        jp1.add(label6);

        jp1.add(jt6);

        jp1.add(jb1);

        jp1.add(jb2);

        jp1.add(jb3);

        jp1.add(jb4);

        jb1.addActionListener(new ActionListener() {

            @Override

            public void actionPerformed(ActionEvent e) {

                String strsql="";

                if(!jt1.getText().isEmpty())

                    strsql+=" and pid="+jt1.getText();

                if(!jt2.getText().isEmpty())

                    strsql+=" and name='"+jt2.getText()+"'";

                if(!jt3.getText().isEmpty())

                    strsql+=" and sex='"+jt3.getText()+"'";

                if(!jt4.getText().isEmpty())

                    strsql+=" and tel='"+jt4.getText()+"'";

                if(!jt5.getText().isEmpty())

                    strsql+=" and address='"+jt5.getText()+"'";

                if(!jt6.getText().isEmpty())

                    strsql+=" and email='"+jt6.getText()+"'";

                List<Person> list = pd.quarybyitems(strsql);

                reset(list);

            }

        });

        jb2.addActionListener(new ActionListener() {

            @Override

            public void actionPerformed(ActionEvent e) {

                if(jt1.getText().isEmpty()||jt2.getText().isEmpty()||jt3.getText().isEmpty()||jt4.getText().isEmpty()||jt5.getText().isEmpty()||jt6.getText().isEmpty())

                    JOptionPane.showMessageDialog(null, "请输入完整！！");

                else {

                    String strsql="("+jt1.getText()+",\""+jt2.getText()+"\",\""+jt3.getText()+"\",\""+jt4.getText()+"\",\""+jt5.getText()+"\",\""+jt6.getText()+"\")";

                    if(pd.update(strsql)) {

                        JOptionPane.showMessageDialog(null, "插入成功");

                         List<Person> list = pd.quaryall();

                         reset(list);

                    }else {

                        JOptionPane.showMessageDialog(null, "插入失败");

                    }

                }

            }

        });

        jb3.addActionListener(new ActionListener() {

            @Override

            public void actionPerformed(ActionEvent e) {

                int pid= (int)table1.getModel().getValueAt(table1.getSelectedRow(), 0);

                pd.deletePersonByID(pid);

                List<Person> list = pd.quaryall();

                reset(list);

            }

        });

        jb4.addActionListener(new ActionListener() {

            @Override

            public void actionPerformed(ActionEvent e) {

                List<Person> list = pd.quaryall();

                reset(list);

            }

        });

        List<Person> list = pd.quaryall();

        reset(list);

        setTitle("我的通讯录");

        setBounds(600,400,600,350);

        setVisible(true);

        setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);

    }

    private void reset(List<Person> list){

        List<Object[]> b=new ArrayList<Object[]>();

        for(Person p:list) {

            Object[] o= {p.getPid(),p.getName(),p.getSex(),p.getTel(),p.getAddress(),p.getEmail()};

            b.add(o);

        }

        Object[][] a = (Object[][])b.toArray(new Object[b.size()][]);

        table1=new JTable(a,Names1);

        getContentPane().removeAll();

        add(jp1,BorderLayout.NORTH);

        add(new JScrollPane(table1),BorderLayout.CENTER);

        validate();

    }

    public static void main(String[] args) {

        new TestFrm();

    }

}

DBUtil包代码

JavaEE-实验三 Java数据库高级编程

 此句话作用 用于设置写入数据库与读取数据库数据编码

若遇到mysql中文编码问题 赶紧戳这里解决mysql编码常见问题分享

package util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

//通用的数据库操作方法

public class DBUtil {

    //sql数据库连接字符串

    private static final String URL="jdbc:mysql://127.0.0.1:3306/mydatabase?useUnicode=true&characterEncoding=utf8";

    //sql用户名 和密码 用作连接用

    private static final String USERNAME="root";

    private static final String PWD="cc123nice";

    public static Connection connection=null;

    public static PreparedStatement pst =null;

    public static ResultSet rs =null;

    /*

     * 得到PreparedStatement减少代码冗余

     */

    public static PreparedStatement getPreparedStatement(String sql,Object[] params) {

        //导入驱动，加载具体驱动类

        try {

            Class.forName("com.mysql.jdbc.Driver");

            //与数据库建立链接

            connection = DriverManager.getConnection(URL, USERNAME, PWD);

            pst=connection.prepareStatement(sql);

            if(params!=null) {

                for(int i=0;i<params.length;i++){

                    pst.setObject(i+1,params[i]);

                }

            }

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return pst;

    }

    /*

     * 最后关闭所有

     */

    public static void closeAll(ResultSet rs,Statement stmt,Connection connection) {

        try {

            if(rs!=null) rs.close();

            if(stmt!=null) stmt.close();

            if(connection!=null) connection.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    /*

     * 通用的增删改 通过传入的sql和obj数组确认语句

     */

    public static boolean executeUpdate(String sql,Object[] params) {

        try {

            int count = getPreparedStatement(sql,params).executeUpdate();

            if(count>0)

                return true;

            else

                return false;

        } catch (SQLException e) {

            e.printStackTrace();

            return false;

        } catch (Exception e) {

            e.printStackTrace();

            return false;

        }finally {

            closeAll(null, pst, connection);

        }

    }

    /*

     * 通用的查询（只能返回到ResultSet）之后必须与具体类型耦合

     */

    public static ResultSet executeQuery(String sql,Object[] params) {

        try {

            rs= getPreparedStatement(sql,params).executeQuery();

            return rs;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;

        } catch (Exception e) {

            e.printStackTrace();

            return null;

        }

    }

}