最近在搞joomla ,自己写了一个简单的批量导入用户. 用户使用Execl把通讯录导出csv,逗号分隔模式,加入密码和用户名字段,,就可以实现批量导入了.
实现功能 批量加入,同时如果这个用户名存在则不加入.
用户信息主要保存在这三张表中:
jos_users 中? jos_core_acl_aro 及 jos_core_acl_groups_aro_map
package db;
import Java.sql.*;
import com.mysql.jdbc.Driver;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.*;
import java.util.HashMap;
public class DB {
HashMap hs=new HashMap();
String sqlurl = "jdbc:mysql://localhost:3306/XXXXl?user=root&password=&useUnicode=true&characterEncoding=utf8";
public DB() {
this.buildConnection();
hs.put("ROOT",17);
hs.put("Registered",18);
hs.put("Author",19);
hs.put("Editor",20);
hs.put("Publisher",21);
hs.put("Manager",23);
hs.put("Administrator",24);
hs.put("Super Administrator",25);
hs.put("USERS",28);
hs.put("Public Frontend",29);
hs.put("Public Backend",30);
}
private Connection m_connection;
public DB(Connection con) {
this.m_connection = con;
}
public Connection getConnection()
{
if (this.m_connection != null)
return this.m_connection;
else {
this.buildConnection();
return this.m_connection;
}
}
private void buildConnection()
{
try{
// this.m_connection =this.m_datasource.getConnection();
String sqlurl = "jdbc:mysql://XXXX:3306/XXXX?user=XXXXX&password=123456&useUnicode=true&characterEncoding=GBK";
Class.forName("com.mysql.jdbc.Driver").newInstance();
// DBCon dbcon=(DBCon)session.getAttribute("dbcon");
this.m_connection = DriverManager.getConnection(sqlurl);
}
catch(Exception ex)
{
System.out.println("errors in database connection");
System.out.println(ex.toString());
}
}
private void closeCon()
{
try{
this.m_connection.close();
this.m_connection=null;
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
public String adduser(int cid)
{
String url="";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection connection = DriverManager.getConnection(sqlurl);
Statement statement = connection.createStatement();
String sql =
"SELECT * FROM category where cid="+cid;
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
url=rs.getString("url");
}
connection.close();
}catch(Exception ex)
{
System.out.print(ex.toString());
}
return url;
}
public static Boolean isNum(String str) {
return str.matches("[0-9]+");
}
/**
* 1 表示存在
* 0 表示不存在
* -1 表示查询出错
* @param username String
* @return int
*/
public int isExist(String username)
{
int isexist=-1;
try{
String sql = "Select username from jos_users where username='" +
username.trim() + "';";
Statement statement = m_connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
//int id1 = 0;
String m_username="";
while (rs.next()) {
//id1 = rs.getInt(1);
m_username=rs.getString(1);
return 1;
}
rs.close();
statement.close();
return 0;
}
catch(Exception e)
{
System.out.print(e.toString());
return -1;
}
}
public Status Addusers(String name,String username,String pass,String email,String usertype,String param)
{
Status s = new Status();
if(this.isExist(username)==0){
if (param.length() == 0)
param = "admin_language=zh-CN\nlanguage=zh-CN\neditor=tinymce\nhelpsite=\ntimezone=8\n\n";
s.stateid = 0;
try {
int group_id = 19;
group_id = (Integer) hs.get(usertype);
PreparedStatement ps = this.m_connection
.prepareStatement("INSERT INTO jos_users (name,username,password,usertype,email,registerDate,params,gid) VALUES(?,?,MD5(\"" +
pass + "\"),?,?,now(),?)");
ps.setString(1, name);
ps.setString(2, username);
ps.setString(3, usertype);
ps.setString(4, email);
ps.setString(5, param);
ps.setString(6,group_id);
ps.execute();
ps.close();
String sql = "Select LAST_INSERT_ID()";
Statement statement = m_connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
int id1 = 0;
while (rs.next()) {
id1 = rs.getInt(1);
}
rs.close();
statement.close();
String section_value = "users";
String value = id1 + "";
ps = this.m_connection.prepareStatement(
"INSERT INTO jos_core_acl_aro (section_value,value,name) VALUES(?,?,?)");
ps.setString(1, section_value);
ps.setString(2, value);
ps.setString(3, name);
ps.execute();
ps.close();
statement = m_connection.createStatement();
rs = statement.executeQuery(sql);
int id2 = 0; //
while (rs.next()) {
id2 = rs.getInt(1);
}
rs.close();
statement.close();
section_value = "";
/*
id parent_id name lft rgt value
17 0 ROOT 1 22 ROOT
18 29 Registered 4 11 Registered
19 18 Author 5 10 Author
20 19 Editor 6 9 Editor
21 20 Publisher 7 8 Publisher
23 30 Manager 14 19 Manager
24 23 Administrator 15 18 Administrator
25 24 Super Administrator 16 17 Super Administrator
28 17 USERS 2 21 USERS
29 28 Public Frontend 3 12 Public Frontend
30 28 Public Backend 13 20 Public Backend