JDBC和XML练习题

JDBC和XML练习题

练习1

数据准备：
在mysql中创建一个数据表employee , 有以下字段
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), – 性别
salary DOUBLE, – 工资
bonus DOUBLE, – 奖金
join_date DATE – 入职日期示
请给 employee 员工表中添加一些数据。

要求：
1.编写一个测试类，在该测试类中使用PrepareStatement 根据name和gender进行数据查询，并将结果打印在控制台。
2.要求编写一个编写一个db.properties , 数据库的数据存放在配置文件中。
3.要求自己编写一个工具类DBUtils , 该类中提供静态获取Connection连接的方法。

#创建数据库
CREATE DATABASE db CHARACTER SET utf8;
#创建employee表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,  
	NAME VARCHAR(10), 
	gender CHAR(1), -- 性别  
	salary DOUBLE, -- 工资  
	bonus DOUBLE, -- 奖金  
	join_date DATE -- 入职日期示
);
#插入数据
INSERT INTO employee(NAME,gender,salary,bonus,join_date) VALUES('关羽','男',7200,5000,'2013-02-24');
INSERT INTO employee(NAME,gender,salary,bonus,join_date) VALUES('张飞','男',3600,8000,'2010-12-02');
INSERT INTO employee(NAME,gender,salary,bonus,join_date) VALUES('刘备','男',9000,6000,'2008-08-08');
INSERT INTO employee(NAME,gender,salary,bonus,join_date) VALUES('孙尚香','女',5000,7000,'2015-10-07');
INSERT INTO employee(NAME,gender,salary,bonus,join_date) VALUES('蔡文姬','女',4500,3000,'2011-03-14');
INSERT INTO employee(NAME,gender,salary,bonus,join_date) VALUES('关羽','女',5200,3000,'2014-04-22');

//db.properties配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db?characterEncoding=UTF-8
username=数据库用户名
password=数据库密码
initialSize=5
maxActive=10
maxWait=3000

//DBUtils工具类
public class DruidUtils {
    //1.定义成员变量
    public static DataSource dataSource;
    //2.静态代码块
    static {
        try {
            //2.1创建属性集对象
            Properties p = new Properties();
            //2.2加载配置文件 Druid连接池不能够主动加载配置文件,需要指定文件
            InputStream inputStream = DruidUtils.class.getClassLoader().getResourceAsStream("db.properties");
            //2.3Properties对象的load方法 从字节流中读取配置信息
            p.load(inputStream);
            //2.4通过工厂类获取连接对象
            dataSource = DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
    }
    
    //3.获取连接方法
    public static Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }

    //4.释放资源
    public static void close(Connection con, Statement statement){
        if(con != null && statement != null){
            try {
                statement.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
            }
        }
    }

    public static void close(Connection con, Statement statement, ResultSet resultSet){
        if(con != null && statement != null && resultSet != null){
            try {
                resultSet.close();
                statement.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
            }
        }
    }
}

//测试类
public class TestEmployee {
    @Test
    public void test() throws SQLException {
        //1.获取连接
        Connection connection = DruidUtils.getConnection();
        //2.获取prepareStatement对象
        PreparedStatement ps = connection.prepareStatement("select * from employee where name = ? and gender = ?");
        //3.设置参数
        ps.setString(1,"关羽");
        ps.setString(2,"男");
        //4.执行查询
        ResultSet resultSet = ps.executeQuery();
        //5.处理结果集
        if (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String gender = resultSet.getString("gender");
            double salary = resultSet.getDouble("salary");
            double bonus = resultSet.getDouble("bonus");
            Date join_date = resultSet.getDate("join_date");
            System.out.println(id + " " + name + " " + gender + " " + salary + " " + bonus + " " + join_date);
        }
        //6.关闭流
        DruidUtils.close(connection,ps,resultSet);
    }
}

练习2

需求：有books.xml，请解析出图书信息，并打印。
book.xml内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<books>
      <book id="01" 出版社="传智出版社">
           <name>Java编程思想</name>
           <author>James</author>
           <price>98.00</price>
           <body>库存：30</body>
      </book>
      <book id="02" 出版社="传智出版社">
           <name>JavaEE从入门到精通</name>
           <author>传智播客</author>
           <price>40.00</price>
           <body>库存：20</body>
      </book>
      <book id="03" 出版社="传智出版社">
           <name>Java开发手册</name>
           <author>阿里巴巴</author>
           <price>15.00</price>
           <body>库存：300</body>
      </book>
</books>

public class TestBook {
    @Test
    public void testBook() throws DocumentException {
        //1.获取xml解析对象
        SAXReader reader = new SAXReader();
        //2.解析xml获取文档对象document
        Document document = reader.read("src/com/learning/xml/book.xml");
        //3.获取根元素
        Element rootElement = document.getRootElement();
        //获取根元素下标签
        List<Element> elements = rootElement.elements();
        for (Element element:elements) {
            //获取图书编号
            String id = element.attributeValue("id");
            System.out.println(id);
            // 获得出版社
            String  publisher = element.attributeValue("出版社");
            System.out.println( publisher);
            //获得book元素节点的子元素节点
            List<Element> elements1 = element.elements();
            for (Element element1:elements1) {
                String name = element1.getName();
                String text = element1.getText();
                System.out.println(name + ":" + text);
            }
        }
    }
}