我现在明白是应该利用java的命令行参数来将文件导入...可是查来查去也不知道具体该怎么搞,再一个问题就是把不同的txt文件的内容导入不同名称的表,如何用PreparedStatemente避免重复代码啊。 You are required to implement a Java program Populate.java that gets the names of the input data files as command line parameters and populate the data contained within them into your database by executing individual insert statements for each row using JDBC. Note that you should use the JDBC PreparedStatement construct when executing the same statement repeatedly. 这个是老师的要求....不晓得大大们会不会嫌英文麻烦...
PreparedStatement用?代替变量.然后加载后setString给赋值.由于PreparedStatement内置了字符过滤 那么就相当于 where name = ' or 1 or '显然没有对应记录.所以数据结果为空.这就体现了PreparedStatement的防注入功能 所以提倡大家只要是动态参数就是用PreparedStatement去代替Statement.况且效率也不错.优化的很好.感觉 你老师 的意思 是让你去写程序 读一个文件夹,得出文件夹下 所有的文件名字,以参数的形式传入你写功能,再用PreparedStatement 去存入数据库
prepareStatement的使用conn = DBUtils.getConnection(); String sql = "select * from users where lastname = ?"; // 这里用问号 st = conn.prepareStatement(sql); st.setString(1,name); // 这里将问号赋值 rs = st.executeQuery();
When a Driver class is loaded, it should create an instance of itself and register it with the DriverManager. you are import java.sql.DriverManager;so, This is the reason why you do not write "Class.forName("com.mysql.jdbc.Driver");", I think.对了 妹子 google LG nexus 4 美国炒到多少刀了? 有现货没?
public class Driver extends NonRegisteringDriver implements java.sql.Driver { // ~ Static fields/initializers // --------------------------------------------- // // Register ourselves with the DriverManager // static { t ry { java.sql.DriverManager.registerDriver(new Driver()); } catch (SQLException E) { throw new RuntimeException("Can't register driver!"); } } // ~ Constructors // ----------------------------------------------------------- /** * Construct a new driver and register it with DriverManager * * @throws SQLException * if a database error occurs. */ public Driver() throws SQLException { // Required for Class.forName().newInstance() } }
说是要用PreparedStatement呢,这个我不是很懂....能给讲讲怎么用在这个问题上面吗,难道要重复的去写代码吗...
You are required to implement a Java program Populate.java that gets the names of the
input data files as command line parameters and populate the data contained within them into
your database by executing individual insert statements for each row using JDBC. Note that
you should use the JDBC PreparedStatement construct when executing the same
statement repeatedly.
这个是老师的要求....不晓得大大们会不会嫌英文麻烦...
那么就相当于 where name = ' or 1 or '显然没有对应记录.所以数据结果为空.这就体现了PreparedStatement的防注入功能
所以提倡大家只要是动态参数就是用PreparedStatement去代替Statement.况且效率也不错.优化的很好.感觉 你老师 的意思 是让你去写程序 读一个文件夹,得出文件夹下 所有的文件名字,以参数的形式传入你写功能,再用PreparedStatement 去存入数据库
String sql = "select * from users where lastname = ?"; // 这里用问号
st = conn.prepareStatement(sql);
st.setString(1,name); // 这里将问号赋值
rs = st.executeQuery();
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class DBUtils {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
}catch(ClassNotFoundException e){
System.out.println("Driver fails to load...");
}
}
public static Connection getConn(){
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=GBK","root","root");
} catch(SQLException e){
System.out.println("link failed to database...");
}
return conn;
}
public static PreparedStatement getPstmt(Connection conn, String sql) {
PreparedStatement ps = null;
try{
ps = conn.prepareStatement(sql);
}catch(SQLException e){
System.out.println("预处理失败");
}
return ps;
}
public static void close(ResultSet rs, PreparedStatement ps, Connection conn){
if(rs != null){
try{
rs.close();
}catch(SQLException e){
System.out.println("关闭异常");
}
}
if(ps != null){
try{
ps.close();
}catch(SQLException e){
System.out.println("关闭异常");
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
System.out.println("关闭异常");
}
}
}
}
当你要使用的时候直接:
Connection conn = DBUtils.getConn();
PreparedStatement pstmt = DBUtils.getPstmt();
然后就可以执行pstmt了
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.util.List;
import java.util.ArrayList;
import java.io.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement; public class db {
public static void main(String[]args){
/*read the properties*/
String s;
int t = 0;
String[] sa=new String[5];
List data = new ArrayList();
try{
BufferedReader in =new BufferedReader(new FileReader(args[0]));
while((s=in.readLine())!=null){
sa[t] =s.substring(0);
t++;
}
}
catch(FileNotFoundException e){
e.printStackTrace();
}
catch(IOException e){
e.printStackTrace();
}
/*connect the DB*/
String url = "jdbc:mysql://"+sa[0]+":"+sa[1]+"/"+sa[2];
String username = sa[3];
String password = sa[4];
Connection conn = null;
PreparedStatement stmt = null;
try{
conn = DriverManager.getConnection(url, username, password);
}catch (SQLException e1) {
e1.printStackTrace();
}
/*put data in DB*/
File file = new File(args[1]);
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
InputStreamReader input = new InputStreamReader(fis);
BufferedReader br = new BufferedReader(input);
String line = null;
String sql = null;
String info[] = null;
String path = file.getAbsolutePath();//得到选择文件的全路径
String fileName = path.substring(path.lastIndexOf("\\")+1, path.lastIndexOf("."));//取得所选文件名
String province = fileName.substring(0,fileName.length()-2);
String cardType = fileName.substring(fileName.length()-2);
try {
while((line = br.readLine())!= null){
info = line.split(",");
sql = "insert into cc(A,B,C)values('"+ info[0] +"','"+info[1]+"','"+info[2]+"')";
stmt = conn.prepareStatement(sql);
stmt.executeUpdate();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
还有就是能麻烦你看看我刚刚贴出来的代码,里面关于preparestatement的用法对不对吗?
CREATE TABLE `student`(
`id` INT NOT NULL AUTO_INCREMENT ,
`filename` VARCHAR(255) , `content` VARCHAR(255) , PRIMARY KEY (`id`) );
CREATE TABLE `route`(
`id` INT NOT NULL AUTO_INCREMENT ,
`filename` VARCHAR(255) , `content` VARCHAR(255) , PRIMARY KEY (`id`) );CREATE TABLE `vehicle`(
`id` INT NOT NULL AUTO_INCREMENT ,
`filename` VARCHAR(255) , `content` VARCHAR(255) , PRIMARY KEY (`id`) );CREATE TABLE `stop`(
`id` INT NOT NULL AUTO_INCREMENT ,
`filename` VARCHAR(255) , `content` VARCHAR(255) , PRIMARY KEY (`id`) );
CREATE TABLE `zone`(
`id` INT NOT NULL AUTO_INCREMENT ,
`filename` VARCHAR(255) , `content` VARCHAR(255) , PRIMARY KEY (`id`) );
package com.test;import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;public class MyTest {
public static void main(String[] args) {
try {
String url = "jdbc:mysql://localhost:3306/users";
String username = "root";
String password = "root";
Connection conn = null;
PreparedStatement stmt = null;
File studentFile=new File("D:/dbtest/student.txt");
File routeFile=new File("D:/dbtest/route.txt");
File stopFile=new File("D:/dbtest/stop.txt");
File vehicleFile=new File("D:/dbtest/vehicle.txt");
File zoneFile=new File("D:/dbtest/zone.txt");
List<File> fileList=new ArrayList<File>();
fileList.add(studentFile);
fileList.add(routeFile);
fileList.add(stopFile);
fileList.add(vehicleFile);
fileList.add(zoneFile);
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, username, password);
PreparedStatement pstm=null;
for (File file : fileList) {
String fileName=file.getName();
fileName = fileName.replace(".txt", "");
System.out.println("fileName..."+fileName);
FileReader fr = new FileReader(file);
BufferedReader bufferedReader=new BufferedReader (fr);
String content=bufferedReader.readLine();
String sql="insert into "+fileName+" (filename,content) values (? , ?)";
pstm =conn.prepareStatement(sql);
pstm.setString(1, fileName);
pstm.setString(2, content);
pstm.execute();
bufferedReader.close();
fr.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}不知道能否满足?
package com.test;import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;public class MyTest {
public static void main(String[] args) {
PreparedStatement pstm=null;
Connection conn = null;
try {
String url = "jdbc:mysql://localhost:3306/users";
String username = "root";
String password = "root";
File studentFile=new File("D:/dbtest/student.txt");
File routeFile=new File("D:/dbtest/route.txt");
File stopFile=new File("D:/dbtest/stop.txt");
File vehicleFile=new File("D:/dbtest/vehicle.txt");
File zoneFile=new File("D:/dbtest/zone.txt");
List<File> fileList=new ArrayList<File>();
fileList.add(studentFile);
fileList.add(routeFile);
fileList.add(stopFile);
fileList.add(vehicleFile);
fileList.add(zoneFile);
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, username, password);
for (File file : fileList) {
String fileName=file.getName();
fileName = fileName.replace(".txt", "");
System.out.println("fileName..."+fileName);
FileReader fr = new FileReader(file);
BufferedReader bufferedReader=new BufferedReader (fr);
String content=bufferedReader.readLine();
String sql="insert into "+fileName+" (filename,content) values (? , ?)";
pstm =conn.prepareStatement(sql);
pstm.setString(1, fileName);
pstm.setString(2, content);
pstm.execute();
bufferedReader.close();
fr.close();
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
pstm.close();
conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}忘记关闭资源了
首先感谢这位大大这么耐心啊,不过我们老师要求的程序可以以下面的命令行执行的
java -classpath .;mysql-connector-java-5.1.18-bin.jar Populate db.properties student.txt vehicle.txt route.txt stop.txt zone.txt 您给出的这个配置文件那里肯定是不行的..那个读入txt文件那块是不是把相应的路径名称换成args[]就可以啊..等我再仔细研究下看看,还有就是您能给解释下这个命令里面 classpath是具体是怎么个意思吗?后面的.和;有什么特别的意思吗
把mysql的jar放到classpath环境变量里面吧
也就是项目中的lib目录,你大概这样理解就可以了
目录结构如下:
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;public class Populate{
public static void main(String[] args) {
PreparedStatement pstm=null;
Connection conn = null;
try {
File propertiesFile=new File(args[0]);
Properties prop = new Properties();
FileInputStream fis = new FileInputStream(propertiesFile);
prop.load(fis);
String username=prop.getProperty("username");
String password=prop.getProperty("password");
String url = prop.getProperty("url");
File studentFile=new File(args[1]);
File vehicleFile=new File(args[2]);
File routeFile=new File(args[3]);
File stopFile=new File(args[4]);
File zoneFile=new File(args[5]);
List<File> fileList=new ArrayList<File>();
fileList.add(studentFile);
fileList.add(routeFile);
fileList.add(stopFile);
fileList.add(vehicleFile);
fileList.add(zoneFile);
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, username, password);
for (File file : fileList) {
String fileName=file.getName();
fileName = fileName.replace(".txt", "");
System.out.println("fileName..."+fileName);
FileReader fr = new FileReader(file);
BufferedReader bufferedReader=new BufferedReader (fr);
String content=bufferedReader.readLine();
String sql="insert into "+fileName+" (filename,content) values (? , ?)";
pstm =conn.prepareStatement(sql);
pstm.setString(1, fileName);
pstm.setString(2, content);
pstm.execute();
bufferedReader.close();
fr.close();
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
pstm.close();
conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
在cmd里面执行如下命令
javac Populate.java
java -classpath .;mysql-connector-java-5.1.18-bin.jar Populate db.properties student.txt vehicle.txt route.txt stop.txt zone.txt
谢谢大大啊~真是好银!!虽然你的代码和我需要的不是很一样,不过帮助我理解了一些问题
但是还是有几个地方不太懂啊
36:: Class.forName("com.mysql.jdbc.Driver");
这句话我是不太明白,不过您看我#17贴的代码里面没有用到这个命令,而且我在您的代码里面删掉这个语句,也是能够正常运行的...能解释下为什么吗?还有就是您写的那个将5个文件的内容导入数据库的方法是很方便啦,不过我们给的那5个文件都是不一样的,里面的属性是不一样的...有的有3个有的有5个的就像比如我们作业要做的是空间数据库..所以数据都是些经纬度的东东,像route的话就有好几个
之前我的实现方式是
while((line = br.readLine())!= null){
info = line.split(",");
sql = "insert into cc(Student,Co,La)values('"+ info[0] +"','"+info[1]+"','"+info[2]+"')";
stmt = conn.prepareStatement(sql);
stmt.executeUpdate();
}
可是我发现这样也只是针对一个文件的...下一个文件属性不一样了,又得重新写一个这样的代码
PreparedStatement pstmt = DBUtils.getPstmt();
这里是不是要把相应才 conn和sql作为参数放到getPstmt(conn,sql)?
那这样的话 你要根据每个txt 做不同的sql语句哦。每个txt对应一条sql吧
那你就要每个File去读取,把每行存都存到相应的表里面的相应的字段里面Class.forName("com.mysql.jdbc.Driver");是注册驱动。
#32
可是我发现这样也只是针对一个文件的...下一个文件属性不一样了,又得重新写一个这样的代码 关于这个问题。你可以把字段什么的设为一个参数传过去,根据文件名称的不同还传
CREATE TABLE `route` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`r1` VARCHAR(255) DEFAULT NULL,
`r2` VARCHAR(255) DEFAULT NULL,
`r3` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`st1` VARCHAR(255) DEFAULT NULL,
`st2` VARCHAR(255) DEFAULT NULL,
`st3` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
CREATE TABLE `vehicle` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`v1` VARCHAR(255) DEFAULT NULL,
`v2` VARCHAR(255) DEFAULT NULL,
`v3` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
CREATE TABLE `zone` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`z1` VARCHAR(255) DEFAULT NULL,
`z2` VARCHAR(255) DEFAULT NULL,
`z3` VARCHAR(255) DEFAULT NULL,
`z4` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
CREATE TABLE `stop` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`s1` VARCHAR(255) DEFAULT NULL,
`s2` VARCHAR(255) DEFAULT NULL,
`s3` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);route.txt内容r1-1,r1-2,r1-3
r2-1,r2-2,r2-3stop.txt 内容s1-1,s1-2,s1-3
s2-1,s2-2,s2-3student.txt内容st1-1,st1-2,st1-3
st2-1,st2-2,st2-3vehicle.txt内容v1-1,v1-2,v1-3
v2-1,v2-2,v2-3zone.txt内容(上面几个txt一行3个数据,一行有4个数据)z1-1,z1-2,z1-3,z1-4
z2-1,z2-2,z2-3,z2-4import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;public class Populate {
public static void main(String[] args) {
PreparedStatement pstm=null;
Connection conn = null;
try {
File propertiesFile=new File(args[0]);
Properties prop = new Properties();
FileInputStream fis = new FileInputStream(propertiesFile);
prop.load(fis);
String username=prop.getProperty("username");
String password=prop.getProperty("password");
String url = prop.getProperty("url");
File studentFile=new File(args[1]);
File vehicleFile=new File(args[2]);
File routeFile=new File(args[3]);
File stopFile=new File(args[4]);
File zoneFile=new File(args[5]);
List<File> fileList=new ArrayList<File>();
fileList.add(studentFile);
fileList.add(routeFile);
fileList.add(stopFile);
fileList.add(vehicleFile);
fileList.add(zoneFile);
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, username, password);
String fileds="";
String routeFields="r1,r2,r3";
String stopFields="s1,s2,s3";
String studentFields="st1,st2,st3";
String vehicleFields="v1,v2,v3";
String zoneFields="z1,z2,z3,z4";
for (File file : fileList) {
String fileName=file.getName();
fileName = fileName.replace(".txt", "");
System.out.println("fileName..."+fileName);
FileReader fr = new FileReader(file);
BufferedReader bufferedReader=new BufferedReader (fr);
String str="";
String[] values=null;
//通过文件名称来判断这个表的fileds
if(fileName.equals("route")){
fileds=routeFields;
}else if(fileName.equals("stop")){
fileds=stopFields;
}else if(fileName.equals("student")){
fileds=studentFields;
}else if(fileName.equals("zone")){
fileds=zoneFields;
}else if(fileName.equals("vehicle")){
fileds=vehicleFields;
}
String[] s=fileds.split(",");
int k=s.length;
String val="";
for(int i =0 ;i<k;i++ ){
if(i<k-1){
val+=" ? , ";
}else{
val+=" ? ";
}
}
while ((str=bufferedReader.readLine())!=null) {
values=str.split(",");
String sql="insert into "+fileName+" ( "+ fileds + " ) values ( " +val+ " )";
System.out.println(sql);
pstm =conn.prepareStatement(sql);
for (int i = 1; i <= k; i++) {
System.out.println("set value.."+values[i-1]);
pstm.setString(i, values[i-1]);//values数组下标从0开始,当pstm设置第一个?的时候 values则是第[0]个
}
pstm.execute();
}
bufferedReader.close();
fr.close();
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
pstm.close();
conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}在cmd里面执行如下命令
javac Populate.java
java -classpath .;mysql-connector-java-5.1.18-bin.jar Populate db.properties student.txt vehicle.txt route.txt stop.txt zone.txt
现在我基本已经可以搞明白了,不过遇到一个新的头疼的问题,就是那5个数据文件里面有一个是route 有一个是zone, 因为是空间数据库的关系,所以那些长串数字要按照经度和纬度搭配输入到一个LINESTRING里面,就是像下面的数据
r1,11,34.031811,-118.290025,34.031839,-118.285539,34.0304,-118.283342
r2,6,34.021592,-118.290083,34.023989,-118.288633,34.022975,-118.287014,34.022292,-118.284772,34.021056,-118.286961,34.020275,-118.287928
r3,7,34.022292,-118.284772,34.022975,-118.287014,34.023989,-118.288633,34.027061,-118.289869,34.029575,-118.289417,34.028975,-118.286039,34.026828,-118.281994
r4,8,34.021056,-118.286961,34.022369,-118.281581,34.022292,-118.284772,34.022975,-118.287014,34.021056,-118.286961,34.020275,-118.287928,34.019303,-118.289136,34.019478,-118.286425这个测试用的数据一共是4行,r1 r2 r3 r4,然后这个route的表示这个样子的
create table route (
RouteID VARCHAR(20) PRIMARY KEY,
Num int(20),
rlocation LineString NOT NULL,
SPATIAL INDEX(rlocation)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
拿r1做例子,就是说r1对应RouteID,11对应Num,剩下的数据按照两个一对的格式存入rlocation,
INSERT INTO Points (name, location) VALUES ( 'r1' ,'11' GeomFromText( ' LINESTRING(34.031811 -118.290025,34.031839 -118.285539,34.0304 -118.283342) ' ) )
就是说每一对经纬度之间用逗号隔开, 每一对经度和纬度之间用空格隔开,可是原始数据给的之前的间隔全是逗号...
你可以用个循环,在把后面的经纬度组成一个长字符串存起来..
后面的我就不写了 你自己写了
you are import java.sql.DriverManager;so,
This is the reason why you do not write "Class.forName("com.mysql.jdbc.Driver");",
I think.对了 妹子 google LG nexus 4 美国炒到多少刀了? 有现货没?
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
// ~ Static fields/initializers
// --------------------------------------------- //
// Register ourselves with the DriverManager
//
static {
t ry {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
// ~ Constructors
// -----------------------------------------------------------
/**
* Construct a new driver and register it with DriverManager
*
* @throws SQLException
* if a database error occurs.
*/
public Driver() throws SQLException {
// Required for Class.forName().newInstance()
}
}