JAVA企业面试题精选 JDBC 1-10

郝文龙

于 2018-08-06 17:15:28 发布

阅读量501

点赞数

分类专栏： java企业面试题精选文章标签： java企业面试题精选

java企业面试题精选专栏收录该内容

44 篇文章

订阅专栏

2.JDBC

2.1.Java数据库编程包含哪些类和接口?Java数据库编程的基本过程是什么?

参考答案:

题目中各问题的答案如下:
1.Java数据库编程包含Connection,ResultSet,PreparedStatement,Statement,DriverManager.
2.Java中访问数据库的步骤如下:
1)注册驱动程序;
2)建立连接;
3)创建Statement;
4)执行SQL语句;
5)处理结果集(若SQL语句为查询语句);
6)关闭连接.

2.2.Statement,PreparedStatement,CallableStatement的区别?

参考答案:

&esmp; Statement,PreparedStatement,CallableStatement的区别有以下几点:
&esmp; 1.Statement是PreparedStatement和CallableStatement的父类;
&esmp; 2.Statement是直接发送SQL语句到数据库,事先没有进行预编译.PreparedStatement会将SQL进行预编译,当SQL语句要重复执行时,数据库会调用以前预编译好的SQL语句,所以PreparedStatement在性能方面会更好;
&esmp; 3.PreparedStatement在执行SQL时,对传入的参数可以进行强制的类型转换.以保证数据格式与底层的数据库格式一致;
&esmp; 4.CallableStatement适用于执行存储过程.

2.3Java中如何进行事务的处理?

参考答案:

Connection类中提供了3个事务处理方法:
setAutoCommit(Boolean autoCommit):设置是否自动提交事务,默认为自动提交,即为true,通过设置false禁止自动提交事务;
commit():提交事务;
rollback():回滚事务.

2.4下述程序是一段简单的基于JDBC的数据库访问代码,实现了以下功能:从数据库中查询product表中的所有记录,然后打印输出到控制台.该代码质量较低,如没有正确处理异常,连接字符串以”魔数”的形式直接存在于代码中等,请用你的思路重新编写程序,完成相同的功能,提高代码质量.

public void printProducts(){
    Connection c = null;
    Statements s = null;
    ResultSet r = null;
    try{

c=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:sid","username","password");
        s=c.createStatement();
        r=s.executeQuery("select id, name, price from product");
        System.out.println("Id\tName\tPrice");
        while(r.next()){
            int x = r.getInt("id");
            String y = r.getString("name");
            float z = r.getFloat("price");
            System.out.println(x + "\t" + y + "\t" + z);
        }
    } catch(Exception e){

    }
}
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

参考答案:

程序改造一:
SQLException一般情况下无法恢复.通常情况下,数据库访问代码在捕获该异常后,在catch块中进行一些必要的处理(例如事务回滚)后,将其直接抛出;或者封装成自定义异常的形式抛出.使用格式如下:

    try{
        //JDBC访问
        return ...;
    } catch (SQLException e){
        //异常处理
        throw new DAOException("访问数据库错误",e);
    } finally {
        //关闭连接
    }
   
   1
2
3
4
5
6
7
8
9

其中DAOException是自定义的异常.

程序改造二:
定义常量类Constant,把连接数据库的信息,定义成常量后再使用.
改造后的程序代码如下所示:

class Constant{
    public static final String URL="jdbc:oracle:thin:@127.0.0.1:1521:sid";
    public static final String USERNAME="username";
    public static final String PASSWORD="password";
}

class DAOException extends Exception{
    public DAOException(){
        super();
    }
    public DAOException(String msg){
        super(msg);
    }
}

public class Test{

    public void printProducts() throws DAOException{
        Connection c = null;
        Statement s = null;
        ResultSet r = null;
        try{
            c = DriverManager.getConnection(Constant.URL,Constant.USERNAME,Constant.PASSWORD);
            s = c.createStatement();
            r = s.executeQuery("select id,name,price from product");
            System.out.println("Id\tName\tPrice");
            while(r.next()){
                int x = r.getInt("id");
                String y = r.getString("name");
                float z = r.getFloat("price");
                System.out.println(x + "\t" + y + "\t" + z);
            }
        } catch (SQLException e){
            throw new DAOException("数据库异常");
        } finally {
            try{
                r.close();
                s.close();
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

2.5.请根据以下要求来完成题目:

会议室预定模块.某公司有多个会议室,以房间号区分.如果某部门需要预定会议室,则会提交预定请求(包含预定开始使用时间,预定结束使用,所预定会议室房间号).

1.设计一个表,保存会议室预定信息.
2.要求采用SQL语句及Java代码段判断在2003-3-10下午3:00~4:00 3号会议室是否空闲.

参考答案:

在Oracle数据库中创建会议表及插入测试数据的SQL语句:

CREATE TABLE MEETING(
    ID NUMBER PRIMARY KEY,
    ROOM_ID VARCHAR2(10),
    ISUSED CHAR,
    BEGIN TIMESTAMP,
    END TIMESTAMP
);

INSERT INTO MEETING VALUES(1,'201',1,TO_DATE('2003-03-10 15:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2003-3-10 16:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO MEETING VALUES(2,'201',1,TO_DATE('2003-03-10 17:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2003-3-10 22:00:00','YYYY-MM-DD HH24:MI:SS'));
   
   1
2
3
4
5
6
7
8
9
10

使用Java代码判断在2003-3-10下午3:00~4:00 3号会议室是否空闲的代码如下所示:

package com.welkin;

import java.sql.*;

pulbic class Test{

    public static void main(String[] args){
        String driverName = "oracle.jdbc.OracleDriver";
        String url = "jdbc:oracle.thin:@127.0.0.1:1521:orcl";
        String username = "welkin";
        String pwd = "123456";
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            Class.forName(driverName);
            con = DriverManager.getConnection(url, username, pwd);
            stmt = con.createStatement();
            String sql = "select isUsed from " + 
            "meeting " + 
            "where ((begin between to_date('2003-03-10 15:00:00'," + "'yyyy-mm-dd hh24:mi:ss') and to_date('2003-03-10 16:00:00'," + "'yyyy-mm-dd hh24:mi:ss'))" + 
            "or(end between to_date('2003-03-10 15:00:00'," + "'yyyy-mm-dd hh24:mi:ss') and to_date('2003-03-10 16:00:00'," + "'yyyy-mm-dd hh24:mi:ss')))" + 
            " and room_id=201";
            if(stmt.execute(sql)){
                rs = stmt.getResultSet();
            }
            StringBuffer sb = new StringBuffer("isFree:");
            while(rs.next()){
                sb.append(rs.getInt(1) + " ");
            }
            System.out.println(sb.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                rs.close();
                stmt.close();
                con.close();
            } catch (Exception e1){
                e1.printStackTrace();
            }
        }
    }
}
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

2.6.假设有以下数据,请分别用XML和JSON表述以下数据.请写一个实体类来定义以下数据类型

表数据(person)

公司	部门	名字	性别	年龄	生日
中国石油	技术部	张三	男	30	1983-4-1
中国石油	销售部	李四	女	30	1983-4-2
中国电信	技术部	王五	男	25	1988-1-2
中国电信	人事部	jack	男	30	1983-4-3

参考答案:

上述数据使用JSON的形式表述如下:

    {"persons":[
    {"公司":"中国石油","部门":"技术部","名字":"张三","性别":"男","年龄":"30","生日":"1983-4-1"},
    {"公司":"中国石油","部门":"销售部","名字":"李四","性别":"女","年龄":"30","生日":"1983-4-2"},
    {"公司":"中国电信","部门":"技术部","名字":"王五","性别":"男","年龄":"25","生日":"1988-1-2"},
    {"公司":"中国电信","部门":"人事部","名字":"jack","性别":"男","年龄":"30","生日":"1983-4-3"}
    ]}
   
   1
2
3
4
5
6

上述数据使用XML表述形式:

<?xml version="1.0" encoding="gb2312"?>
<persons>
    <person>
        <公司>中国石油</公司>
        <部门>技术部</部门>
        <名字>张三</名字>
        <性别>男</性别>
        <年龄>30</年龄>
        <生日>1983-4-1</生日>
    </person>
    <person>
        <公司>中国石油</公司>
        <部门>销售部</部门>
        <名字>李四</名字>
        <性别>女</性别>
        <年龄>30</年龄>
        <生日>1983-4-2</生日>
    </person>
    <person>
        <公司>中国电信</公司>
        <部门>技术部</部门>
        <名字>王五</名字>
        <性别>男</性别>
        <年龄>25</年龄>
        <生日>1988-1-2</生日>
    </person>
    <person>
        <公司>中国电信</公司>
        <部门>人事部</部门>
        <名字>jack</名字>
        <性别>男</性别>
        <年龄>30</年龄>
        <生日>1983-4-3</生日>
    </person>
</persons>
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

使用实体类定义如下:

public class Person{

    private String company;
    private String dept;
    private String name;
    private String gender;
    private int age;
    private String birthday;

    public String getCompany(){
        return company;
    }

    public void setCompany(String company){
        this.company = company;
    }

    public String getDept(){
        return dept;
    }

    public void setDept(String dept){
        this.dept = dept;
    }

    public String getName(){
        return name;
    }

    public void setName(String name){
        this.name = name;
    }

    public String getGender(){
        return gender;
    }

    public void setGender(String gender){
        this.gender = gender;
    }

    public int getAge(){
        return age;
    }

    public void setAge(int age){
        this.age = age;
    }

    public String getBirthday(){
        return birthday;
    }

    public void setBirthday(String birthday){
        this.birthday = birthday;
    }
}
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

2.7.使用Java语言写一个连接Oracle数据库的程序,能够完成修改和查询工作

参考答案:

使用JDBC连接Oracle数据库,并完成修改和查询功能的代码如下:
1.创建数据库表SERVICE_DETAIL,SQL语句如下所示:

CREATE TABLE SERVICE_DETAIL(
    ID NUMBER(11)        CONSTRAINT SERVICE DETAIL ID PK PRIMARY KEY,
    SERVICE_ID NUMBER(11),
    HOST VARCHAR2(15),
    USER_NAME VARCHAR2(50),
    PID NUMBER(11),
    LOGIN_TIME DATE,
    LOGOUT_TIME DATE,
    DURATION NUMBER(20,9),
    COST NUMBER(20,6)
);
   
   1
2
3
4
5
6
7
8
9
10
11

2.db.properties文件包含连接数据库的信息

jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL
jdbc.user=welkin
jdbc.password=123456
   
   1
2
3
4

3.BaseDAO类包含打开和关闭连接的方法

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class BaseDAO{

    private static Properties properties = new Properties();
    private static String driver = properties.getPropety("jdbc.driver");
    private static String url = properties.getProperty("jdbc.url");
    private static String user = properties.getProperty("jdbc.user");
    private static String pwd = properties.getProperty("jdbc.password");

    static{
        try{
            //加载配置文件
            properties.load(BaseDAO.class.getClassLoader().getResourceAsStream("com/welkin/test/daodemo/db.properties");
            driver = properties.getProperty("jdbc.driver");
            url = properties.getProperty("jdbc.url");
            user = properties.getProperty("jdbc.user");
            pwd = properties.getProperty("jdbc.password");
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    protected static Connection openConnection() throws SQLException{
        return DriverManager.getConnection(url, user, pwd);
    }

    protected static void closeConnection(Connection con){
        if(con != null){
            try{
                con.close();
            } catch (SQLException e){

            }
        }
    }
}
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

4.ServiceDetail类是数据库表和Java类的映射

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

public class ServiceDetail implements Serializable{

    private static final long serialVersionUID = 1L;
    private Integer id;
    private Integer serviceid;
    private String host;
    private String userName;
    private Integer pid;
    private Date loginTime;
    private Date logoutTime;
    private Integer duration;
    private BigDecimal cost;

    public ServiceDetail(){

    }

    public Integer getId(){
        return id;
    }

    public void setId(Integer id){
        this.id = id;
    }

    public Integer getServiceid(){
        return serviceid;
    }

    public void setServiceid(Integer serviceid){
        this.serviceid = serviceid;
    }

    public String getHost(){
        return host;
    }

    public void setHost(String host){
        this.host = host;
    }

    public String getUserName(){
        return userName;
    }

    public void setUserName(){
        this.userName = userName;
    }

    public Integer getPid(){
        return pid;
    }

    public void setPid(Integer pid){
        this.pid = pid;
    }

    public Date getLoginTime(){
        return loginTime;
    }

    public void setLoginTime(Date loginTime){
        this.loginTime = loginTime;
    }

    public Date getLogoutTime(){
        return logoutTime;
    }

    public void setLogoutTime(Date logoutTime){
        this.logoutTime = logoutTime;
    }

    public Integer getDuration(){
        return durantion;
    }

    public void setDuration(Integer duration){
        this.duration = duration;
    }

    public BigDecimal getCost(){
        return cost;
    }

    public void setCost(BigDecimal cost){
        this.cost = cost;
    }

    @Override
    public String toString(){
        return "ServiceDetail [id=" + id + ", serviceid=" + serviceid 
               + ", host" + host + ", userName=" + userName + ", pid=" + pid 
               + ", loginTime=" + loginTime + ", logoutTime=" + logoutTime 
               + ", durantion=" + duration + ", cost=" + cost + "]";
    }
}   
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

5.实现对ServiceDetail表的更新和查询.

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.welkin.test.daodemo.entity.ServiceDetail;

public class ServiceDetailDAO extends BaseDAO{

    private static final String UPDATE = "update SERVICE_DETAIL set "
    + "SERVICE_ID=?, HOST=?, USER_NAME=?, PID=?, LOGIN_TIME=?, "
    + "LOGOUT_TIME=?, DURATION=?, COST=? where ID=?";
    priavte static final String FIND_ALL="select ID, SERVICE_ID, HOST, USER_NAME, PID,"
    + "LOGIN_TIME, LOGOUT_TIME, DURATION, COST from SERVICE_DETAIL";

    public List<ServiceDetail> findAll(){
        Connection con = null;
        PreparedStatement stmt = null;
        try{
            con = openConnection();
            stmt = com.prepareStatement(FIND_ALL);
            ResultSet rs = stmt.executeQuery();
            List<ServiceDetail> list = new ArrayList<ServiceDetail>();
            while(rs.next()){
                list.add(toServiceDetail(rs));
            }
            rs.close();
            stmt.close();
            return list;
        } catch (SQLException e) {
            System.out.println("数据库访问异常!");
            throw new RuntimeException(e);
        } finally {
            closeConnection(con);
        }
    }

    public void update(ServiceDetail serviceDetail){
        Connection con = null;
        PreparedStatement stmt = null;
        try{
            con = openConnection();
            stmt = con.prepareStatement(UPDATE);
            stmt.setInt(1,serviceDetail.getServiceid());
            stmt.setString(2,serviceDetail.getHost());
            stmt.setString(3,serviceDetail.getUserName());
            stmt.setInt(4,serviceDetail.getPid());
            stmt.setDate(5,new Date(serviceDetail.getLoginTime().getTime());
            stmt.setDate(6,Date(serviceDetail.getLogoutTime().getTime()));
            stmt.setTimestamp(5,new Timestamp(serviceDetail.getLoginTime().getTime()));
            stmt.setTimestamp(6,Timestamp(serviceDetail.getLogoutTime().getTime()));
            stmt.setInt(7, serviceDetail.getDuration());
            stmt.setBigDecimal(8,serviceDetail.getCost());
            stmt.setInt(9,serviceDetail.getId());

            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            System.out.println("数据库访问异常!");
            throw new RuntimeException(e);
        }
    }

    private ServiceDetail toServiceDetail(ResultSet rs) throws SQLException{
        ServiceDetail serviceDetail = new ServiceDetail();
        serviceDetail.setId(rs.getInt("ID"));
        serviceDetail.setServiceid(rs.getInt("SERVICE_ID"));
        serviceDetail.setHost(rs.geString("HOST"));
        serviceDetail.setUserName(rs.getString("USER_NAME"));
        serviceDetail.setPid(rs.getInt("PID");
        serviceDetail.setLoginTime(rs.getTimestamp("LOGIN_TIME"));
        serviceDetail.setLogoutTime(rs.getTimestamp("LOGOUT_TIME"));
        serviceDetail.setDuration(rs.getInt("DURATION"));
        serviceDetail.setCost(rs.getBigDecimal("COST"));
        return serviceDetail;
    }
}
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

2.8.以下数据库操作的程序片段如何改进会更好

    try{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","123456");
        Statement stmt = conn.createStatement();
        String sql = "select * from T_User where username='" + name + "' and password='" + password + "'";
        ResultSet rs = stmt.executeQuery(sql);
        if(rs.next()){
            System.out.println("User Name and Password is correct!");
        } else {
            System.out.println("User Name and Password pair is invalidate");
        }
    } catch (Exception e){
        e.printStackTrace();
    }
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14

参考答案:

使用PreparedStatement防止SQL注入,改进的代码如下所示:

    try{
         Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","123456");
         String sql = "select count(*) from t_user where username=? and password=?";
         PreparedStatement ps = conn.prepareStatement(sql);
         ps.setString(1,username);
         ps.setString(2,password);
         Resultset rs = ps.executeQuery();
         rs.next();
         int count = rs.getInt(1);
         if(count == 1){
            System.out.println("User Name and Password is correct!");
         } else {
            System.out.println("User Name and Password pair is invalidate");
         }
    } catch (Exception e){
        e.printStackTrace();
    }
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.9.写出一段JDBC连接本机MySQL数据库的代码

参考答案:

Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost/test";
Stirng user='root';
String password='root';
Connection conn = DriverManager.getConnection(url,user,password);
   
   1
2
3
4
5

2.10.利用Java IO流编写一个SQL语句执行工具,功能类似于(Oracle sqlplus)

例如:输入select * from t_user按回车,可以显示如下结果:

id name age gender

1 张三 20 男

2 李四 21 女

输入insert into t_user(id,name,age,gender)values(1,’王五’,20,’男’)按回车,控制台显示结果:已增加一条记录.

参考答案:

public class Q010{

    public static void main(String args[]){
        Scanner in = new Scanner(System.in);
        String sql = in.nextLine();
        sql = sql.trim();
        System.out.println(sql);
        Connection con = Q010.getConnection();
        PreparedStatement pst = con.prepareStatement(sql);
        if(sql.startWith("select"){
            ResultSet rs = pst.executeQuery();
            System.out.println("id name age gender");
            System.out.println("-------------------");
            while(rs.next()){
                System.out.println(rs.getString("id" + "  " + rs.getString("name") + "  "
                + rs.getString("age") + "  " + rs.getString("gender"));
            }
        } else if(sql.startWith("insert"){
            pst.executeUpdate();
            System.out.println("增加一条数据");
        }
    }

    //下面的方法是使用JDBC连接Oracle数据库
    public static Connection getConnection() throws Exception{
        Class.forName("oracle.jdbc.OracleDriver");
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCl","welkin","123456");
        return con;
    }
}
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30