JDBC+io练习题

Posted on | In |

题目 资源下载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
有一个数据库表，表结构为：
Xh  varchar(20)
Xm  varchar(20)
Bj   varchar(20)
Kc   varchar(20)
Cj   number(12)
1、采用DbDefiner类，创建数据库表。
2、有一个超过10万行的文本文件，其每一行的格式为：
	学号，姓名，班级，课程，成绩<CRLF>
	请解析每一行文件数据，采用JDBC批处理的方式，尽可能高效的将其全部数据写入数据库表中(数据文件见附件，不处理重复)。
3、将上题中，数据库表中的数据导出为以下格式到一个文本文件：
+----------------------------------------------------------------------------------------------+
| 学号       |     姓名      |     班级     |    课程    |  成绩   |
+----------------------------------------------------------------------------------------------+
| SN0001     |     张三      |     一班     |    数学    |  100    |
| SN0002     |     李四      |     一班     |    数学    |  100    |
+----------------------------------------------------------------------------------------------+
注意格式对齐！

注意：
数据库连接使用 jdbc.conf 配置文件
数据库连接，使用 ConnectionFactory

解

建表语句

1
2
3
4
5
6
7
8
9
10
drop database if exists TestWeek1;
create database TestWeek1 DEFAULT CHARACTER SET utf8;
use TestWeek1;
CREATE TABLE grade (
  xh varchar(20) NOT NULL  COMMENT '学号',
  xm varchar(20) NOT NULL  COMMENT '姓名',
  bj varchar(20) NOT NULL  COMMENT '班级',
  kc varchar(20) NOT NULL  COMMENT '课程',
  cj int(12) NOT NULL  COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

GradeUtil

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
package com.fangjun.esensoft2.util;

import java.awt.AWTException;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.esen.util.FileUtils;
import com.fangjun.esensoft2.pojo.Grade;

public class GradeUtil {
	public static void main(String[] args) throws IOException, InterruptedException, AWTException {

        String fileName = "140k_products.txt";

        List<Grade> grades = file2list(fileName);

        System.out.println(grades.size());

    }

    public static List<Grade> file2list(String fileName) throws IOException {
        File f = new File(fileName);
        List<String> lines = FileUtils.readLines(f,"UTF-8");
        List<Grade> products = new ArrayList<>();
        for (String line : lines) {
            Grade p = line2Grade(line);
            products.add(p);
        }
        return products;
    }

    private static Grade line2Grade(String line) {
        Grade p = new Grade();
        String[] fields = line.split(",");
        p.setXh(fields[0]);
        p.setXm(fields[1]);
        p.setBj(fields[2]);
        p.setKc(fields[3]);
        p.setCj(Integer.valueOf(fields[4]));

        return p;
    }

}

Grade

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
package com.fangjun.esensoft2.pojo;

public class Grade {
	private String xh;
	private String xm;
	private String bj;
	private String kc;
	private int cj;
	public String getXh() {
		return xh;
	}
	public void setXh(String xh) {
		this.xh = xh;
	}
	public String getXm() {
		return xm;
	}
	public void setXm(String xm) {
		this.xm = xm;
	}
	public String getBj() {
		return bj;
	}
	public void setBj(String bj) {
		this.bj = bj;
	}
	public String getKc() {
		return kc;
	}
	public void setKc(String kc) {
		this.kc = kc;
	}
	public int getCj() {
		return cj;
	}
	public void setCj(int cj) {
		this.cj = cj;
	}


}

test

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
package com.fangjun.esensoft2.test;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sound.midi.Patch;

import com.esen.jdbc.ConnectionFactory;
import com.esen.jdbc.SimpleConnectionFactory;
import com.esen.util.FileUtils;
import com.esen.util.encyptor.RSA_KeyFuncs;
import com.fangjun.esensoft2.pojo.Grade;
import com.fangjun.esensoft2.util.GradeUtil;
import com.vertica.dsi.core.utilities.StmtPropertyKey;

public class Test {
	public static void main(String[] args) throws IOException {
		List<Grade>grades=GradeUtil
				.file2list("/home/demo/eclipse-workspace/esensoft2/src/main/java/grade.txt");
		System.out.println("数据行数"+grades.size());

		//insertGrade( grades);

		out2file(grades);

	}

	static void insertGrade(List<Grade> grades) {
		String tableName="grade";
		String driver="com.mysql.jdbc.Driver";
	    String url="jdbc:mysql://127.0.0.1:3306/TestWeek1";
	    String user="root";
	    String pwd="admin";

	    SimpleConnectionFactory fct=new SimpleConnectionFactory(driver,url,user,pwd, "debug");
	    Connection conn;
		try {
			conn = fct.getConnection();
			try {

				String sql="insert into "+tableName+" values(?,?,?,?,?)";
				PreparedStatement patmt=conn.prepareStatement(sql);
				try {
					for(Grade grade:grades) {
						patmt.setString(1, grade.getXh());
						patmt.setString(2, grade.getBj());
						patmt.setString(3, grade.getXm());
						patmt.setString(4, grade.getKc());
						patmt.setInt(5, grade.getCj());
						patmt.addBatch();
					}
					patmt.executeBatch();
				} catch (Exception e) {
					// TODO: handle exception
				}finally {
					patmt.close();
				}

			} finally {
				// TODO: handle finally clause
		       conn.close();

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {

		}

	}
	static void out2file(List<Grade> grades) {
		File f = new File("/home/demo/Desktop/task_record/gradeOut.txt");
		try {
			FileWriter fw=new FileWriter(f);
			PrintWriter pw=new PrintWriter(fw);
			pw.println("+----------------------------------------------------------------------------------------------+");
			pw.println("| 学号       |     姓名      |     班级     |    课程    |  成绩   |");
			for(Grade grade:grades) {
				pw.println("+----------------------------------------------------------------------------------------------+");
				pw.println("|  "+grade.getXh()+"       |     "+grade.getXm()+"      |    "
			           +grade.getBj()+"     |    "+grade.getKc()+"    |  "+grade.getCj()+"   |");
			}
			System.out.println("end--------------------");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

pom.xml

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
102
103
104
105
106
107
108
109
110
111
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.fangjun</groupId>
  <artifactId>esensoft2</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>esensoft2</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>

    <dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    	<version>5.1.5</version>
    </dependency>
    <dependency>
       <groupId>javax.mail</groupId>
      <artifactId>mail</artifactId>
       <version>1.4.1</version>
         <exclusions>
              <exclusion>
                 <groupId>javax.activation</groupId>
                 <artifactId>activation</artifactId>
               </exclusion>
          </exclusions>
    </dependency>

    <dependency>
  <groupId>com.esensoft</groupId>
  <artifactId>util</artifactId>
  <version>3.1</version>
</dependency>
<dependency>
  <groupId>com.esen.jdbc</groupId>
  <artifactId>jdbct4</artifactId>
  <version>2.4.0</version>
</dependency>
<dependency>
  <groupId>jdbc</groupId>
  <artifactId>jdbc</artifactId>
  <version>2.0</version>
  <type>pom</type>
</dependency>
<dependency>
  <groupId>jdbc</groupId>
  <artifactId>jdbc-stdext</artifactId>
  <version>2.0</version>
  <type>pom</type>
</dependency>
<dependency>
  <groupId>com.esen</groupId>
  <artifactId>esenbase</artifactId>
  <version>1.1.1-SNAPSHOT</version>
</dependency>
  </dependencies>

  <!-- Nexus配置 -->
  <repositories>
<repository>
<id>esenSnapshots</id>
<name>esenSnapshots</name>
<releases>
<enabled>true</enabled>
<updatePolicy>always</updatePolicy>
<checksumPolicy>warn</checksumPolicy>
</releases>
<snapshots>
<enabled>true</enabled>
<updatePolicy>always</updatePolicy>
<checksumPolicy>fail</checksumPolicy>
</snapshots>
<url>http://cc:8081/nexus/content/groups/public</url>
<layout>default</layout>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>esenSnapshots</id>
<name>esenSnapshots</name>
<releases>
<enabled>true</enabled>
<updatePolicy>always</updatePolicy>
<checksumPolicy>warn</checksumPolicy>
</releases>
<snapshots>
<enabled>true</enabled>
<updatePolicy>always</updatePolicy>
<checksumPolicy>fail</checksumPolicy>
</snapshots>
<url>http://cc:8081/nexus/content/groups/public</url>
<layout>default</layout>
</pluginRepository>
</pluginRepositories>



</project>