DBOpenHelper 类连接数据库代码
package com.casic.hp.party.config;
import android.os.Message;
import android.util.Log;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Set;
public class DBOpenHelper {
private static String driver = "com.mysql.jdbc.Driver";//MySQL 驱动
private static String url = "jdbc:mysql://127.0.0.1:3306/dangjian";//MYSQL数据库连接Url
private static String user = "XXX";//用户名
private static String password = "XXX";//密码
/**
* 连接数据库
* */
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driver);//获取MYSQL驱动
conn = (Connection) DriverManager.getConnection(url, user, password);//获取连接
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库
* */
public static void closeAll(Connection conn, PreparedStatement ps){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭数据库
* */
public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
DBService类
package com.casic.hp.party.config;
import android.telephony.PhoneNumberUtils;
import android.util.Log;
import com.casic.hp.party.casiccul.CasicculCategory;
import com.mysql.jdbc.StringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
public class DBService {
private static Connection conn=null; //打开数据库对象
private static PreparedStatement ps=null;//操作整合sql语句的对象
private static ResultSet rs=null;//查询结果的集合
//DBService 对象
public static DBService dbService=null;
/**
* 构造方法 私有化
* */
private DBService(){
}
/**
* 获取MySQL数据库单例类对象
* */
public static DBService getDbService(){
if(dbService==null){
dbService=new DBService();
}
return dbService;
}
/**
* 获取table 列表信息查询
* */
public static List<String > getCategoryData(){
//结果存放集合
List<String> list=new ArrayList<String>();
PreparedStatement ps = null;
ResultSet rs = null;;
//MySQL 语句
String sql="SELECT * FROM category WHERE category_parent_uid ="+"'72be66cc092240d5a60c2504c02c07d4'"+" GROUP BY category_order";
//获取链接数据库对象
Connection conn= DBOpenHelper.getConn();
try {
if(conn!=null&&(!conn.isClosed())){
ps= (PreparedStatement) conn.prepareStatement(sql);
if(ps!=null){
rs= ps.executeQuery();
if(rs!=null){
while(rs.next()){
CasicculCategory cu=new CasicculCategory();
// cu.setCategory_parent_uid(rs.getString("uid"));
cu.setCategory_name(rs.getString("category_name"));
// cu.setCategory_source(rs.getString("category_source"));
// cu.setCategory_parent_uid(rs.getString("category_parent_uid"));
//cu.setCategory_order(rs.getString("category_order"));
list.add(rs.getString("category_name").toString());
}
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
return list;
}
public static List<String > getCategoryDatahp(){
//结果存放集合
List<String> list=new ArrayList<String>();
PreparedStatement ps = null;
ResultSet rs = null;;
//MySQL 语句
String sql="SELECT * FROM category WHERE category_parent_uid ="+"'1f5b1d01fce949df9ec12be4e8bd7f76'"+" GROUP BY category_order";
//获取链接数据库对象
Connection conn= DBOpenHelper.getConn();
try {
if(conn!=null&&(!conn.isClosed())){
ps= (PreparedStatement) conn.prepareStatement(sql);
if(ps!=null){
rs= ps.executeQuery();
if(rs!=null){
while(rs.next()){
CasicculCategory cu=new CasicculCategory();
// cu.setCategory_parent_uid(rs.getString("uid"));
cu.setCategory_name(rs.getString("category_name"));
// cu.setCategory_source(rs.getString("category_source"));
// cu.setCategory_parent_uid(rs.getString("category_parent_uid"));
//cu.setCategory_order(rs.getString("category_order"));
list.add(rs.getString("category_name").toString());
}
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
return list;
}
/**
* 获取table category 列表信息查询
* */
public static List<CasicculCategory > getCasicculCategoryDataAll(){
//结果存放集合
List<CasicculCategory> list=new ArrayList<CasicculCategory>();
PreparedStatement ps = null;
ResultSet rs = null;;
//MySQL 语句
String sql="SELECT * FROM category WHERE category_parent_uid ="+"'72be66cc092240d5a60c2504c02c07d4'"+" GROUP BY category_order";
//获取链接数据库对象
Connection conn= DBOpenHelper.getConn();
try {
if(conn!=null&&(!conn.isClosed())){
ps= (PreparedStatement) conn.prepareStatement(sql);
if(ps!=null){
rs= ps.executeQuery();
if(rs!=null){
while(rs.next()){
CasicculCategory cu=new CasicculCategory();
cu.setUid(rs.getString("uid"));
cu.setCategory_name(rs.getString("category_name"));
cu.setCategory_source(rs.getString("category_source"));
cu.setCategory_parent_uid(rs.getString("category_parent_uid"));
cu.setCategory_order(rs.getString("category_order"));
list.add(cu);
}
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
DBOpenHelper.closeAll(conn,ps,rs);//关闭相关操作
return list;
}
/* *
* 修改数据库中某个对象的状态 改
*
public int updateUserData(String phone){
int result=-1;
if(!StringUtils.isNullOrEmpty(phone)){
//获取链接数据库对象
conn= DBOpenHelper.getConn();
//MySQL 语句
String sql="update category set state=? where phone=?";
try {
boolean closed=conn.isClosed();
if(conn!=null&&(!closed)){
ps= (PreparedStatement) conn.prepareStatement(sql);
ps.setString(1,"1");//第一个参数state 一定要和上面SQL语句字段顺序一致
ps.setString(2,phone);//第二个参数 phone 一定要和上面SQL语句字段顺序一致
result=ps.executeUpdate();//返回1 执行成功
}
} catch (SQLException e) {
e.printStackTrace();
}
}
DBOpenHelper.closeAll(conn,ps);//关闭相关操作
return result;
}
*
* 批量向数据库插入数据 增
*
public int insertUserData(List<CasicculCategory> list){
int result=-1;
if((list!=null)&&(list.size()>0)){
//获取链接数据库对象
conn= DBOpenHelper.getConn();
//MySQL 语句
String sql="INSERT INTO category (name,phone,content,state) VALUES (?,?,?,?)";
try {
boolean closed=conn.isClosed();
if((conn!=null)&&(!closed)){
for(CasicculCategory user:list){
ps= (PreparedStatement) conn.prepareStatement(sql);
String name=user.getName();
String phone=user.getPhone();
String content=user.getContent();
String state=user.getState();
ps.setString(1,name);//第一个参数 name 规则同上
ps.setString(2,phone);//第二个参数 phone 规则同上
ps.setString(3,content);//第三个参数 content 规则同上
ps.setString(4,state);//第四个参数 state 规则同上
result=ps.executeUpdate();//返回1 执行成功
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
DBOpenHelper.closeAll(conn,ps);//关闭相关操作
return result;
}
*
* 删除数据 删
*
public int delUserData(String phone){
int result=-1;
if((!StringUtils.isNullOrEmpty(phone))){
//获取链接数据库对象
conn= DBOpenHelper.getConn();
//MySQL 语句
String sql="delete from user where phone=?";
try {
boolean closed=conn.isClosed();
if((conn!=null)&&(!closed)){
ps= (PreparedStatement) conn.prepareStatement(sql);
ps.setString(1, phone);
result=ps.executeUpdate();//返回1 执行成功
}
} catch (SQLException e) {
e.printStackTrace();
}
}
DBOpenHelper.closeAll(conn,ps);//关闭相关操作
return result;
}
*/
}
其他类调用方式
public static Handler handler = new Handler(new Handler.Callback() {
@Override
public boolean handleMessage(Message message) {
// super.handleMessage(message);
switch (message.what) {
case ht:
//获取数据
System.out.println("tabName_HT11:"+(String) message.obj);
String objects =message.obj.toString();
tabName_HT1[0] =message.obj.toString();
Bundle bundle = message.getData();
bundle.getStringArray("str");
String[] arr2 = new String[bundle.getStringArray("str").length];
arr2=bundle.getStringArray("str");
System.out.println("bundle.getStringArray--> bundle: " + Arrays.toString(bundle.getStringArray("str")));
/*for(int i=0;i<arr2.length;i++){
listht.add(arr2[i].toString());
System.out.println("tabName_HT1[0]arr2["+i+"]:"+arr2[i].toString());
}*/
for(int i=0;i<arr2.length;i++){
arr33[i]=arr2[i];
System.out.println("tabName_HT1[0]arr2["+i+"]:"+arr2[i].toString());
System.out.println("tabName_HT1 arr33["+i+"] " + arr33[i].toString());
}
break;
case hp:
//获取数据
System.out.println("tabName_HT11:"+(String) message.obj);
String objectshp =message.obj.toString();
tabName_HT1[0] =message.obj.toString();
Bundle bundlehp = message.getData();
bundlehp.getStringArray("str");
String[] arrhp = new String[bundlehp.getStringArray("str").length];
arrhp=bundlehp.getStringArray("str");
System.out.println("bundlehp.getStringArray--> bundle: " + Arrays.toString(bundlehp.getStringArray("str")));
/* for(int i=0;i<arrhp.length;i++){
listhp.add(arrhp[i].toString());
System.out.println("tabName_HP[0]arrhp["+i+"]:"+arrhp[i].toString());
}
*/
for(int i=0;i<arrhp.length;i++){
arr44[i]=arrhp[i];
System.out.println("tabName_HP[0]arrhp["+i+"]:"+arrhp[i].toString());
System.out.println("tabName_HP arr44["+i+"] " + arr44[i].toString());
}
break;
case all:
//获取数据
System.out.println("list:"+(String) message.obj);
String objectsall =message.obj.toString();
//tabName_HT1[0] =message.obj.toString();
Bundle bundleall = message.getData();
bundleall.getStringArray("str");
// List<CasicculCategory> listCategory ;
String[] arrall = new String[bundleall.getStringArray("str").length];
arrall=bundleall.getStringArray("str");
System.out.println("bundleall.getStringArray--> bundle: " + Arrays.toString(bundleall.getStringArray("str")));
/* for(int i=0;i<arrall.length;i++){
arr44[i]=arrhp[i];
System.out.println("tabName_HP[0]arrhp["+i+"]:"+arrhp[i].toString());
System.out.println("tabName_HP arr44["+i+"] " + arr44[i].toString());
}
*/
break;
}
return false;
}
});
public static void getTableNameHT(){
new Thread(new Runnable() {
@Override
public void run() {
List<String> listCategory =
DBService.getCategoryData();
Message msg = new Message();
if (listCategory == null) {
//非UI线程不要试着去操作界面
Log.e("DbConnection", "getUserTableName:fail");
} else {
//String ss = new String();
Object[] objects = listCategory.toArray();//返回Object数组
System.out.println("objects:"+Arrays.toString(objects));
String[] arr = new String[listCategory.size()];
listCategory.toArray(arr);//将转化后的数组放入已经创建好的对象中
System.out.println("strings1:"+Arrays.toString(arr));
msg.what=ht;
msg.obj=Arrays.toString(arr);
Bundle data = new Bundle();
data.putStringArray("str", arr);
msg.setData(data);
// msg.sendToTarget();
//return Arrays.toString(arr);
}
handler.sendMessage(msg);
}
}).start();
//new String[]{tabName_HT1[0]} ;
System.out.println("tabName_HT12[0]2 " + tabName_HT12[0]);
// return tabName_HT12;
}
public static void getTableNameHP(){
new Thread(new Runnable() {
@Override
public void run() {
List<String> listCategoryhp =
DBService.getCategoryDatahp();
Message msg = new Message();
if (listCategoryhp == null) {
//非UI线程不要试着去操作界面
Log.e("DbConnection", "getUserTableName:fail");
} else {
//String ss = new String();
Object[] objects = listCategoryhp.toArray();//返回Object数组
System.out.println("objects:"+Arrays.toString(objects));
String[] arr = new String[listCategoryhp.size()];
listCategoryhp.toArray(arr);//将转化后的数组放入已经创建好的对象中
System.out.println("strings1:"+Arrays.toString(arr));
msg.what=hp;
msg.obj=Arrays.toString(arr);
Bundle data = new Bundle();
data.putStringArray("str", arr);
msg.setData(data);
// msg.sendToTarget();
//return Arrays.toString(arr);
}
handler.sendMessage(msg);
}
}).start();
}
public static void getTablelistCategoryhp(){
new Thread(new Runnable() {
@Override
public void run() {
List<CasicculCategory> listCategory = DBService.getCasicculCategoryDataAll();
//listCategoryhp= DBService.getCasicculCategoryDataAll();
Message msg = new Message();
if (listCategory == null) {
//非UI线程不要试着去操作界面
Log.e("DbConnection", "getUserTableName:fail");
} else {
//String ss = new String();
Object[] objects = listCategory.toArray();//返回Object数组
System.out.println("objects list:"+Arrays.toString(objects));
String[] arr = new String[listCategory.size()];
// listCategory.toArray(arr);//将转化后的数组放入已经创建好的对象中
// System.out.println("strings1 list:"+Arrays.toString(arr));
CasicculCategory cc =new CasicculCategory();
String uu=null;
for(CasicculCategory jj:listCategory)
{
cc.setUid(jj.getUid());
cc.setCategory_name(jj.getCategory_name());
cc.setCategory_source(jj.getCategory_source());
cc.setCategory_parent_uid(jj.getCategory_parent_uid());
cc.setCategory_order(jj.getCategory_order());
listCategoryhp.add(cc);
uu= jj.getCategory_source().toString()+"/"+jj.getUid().toString();
System.out.println("strings1 uu:"+uu);
}
//listCategoryhp.add(cc);
msg.what=all;
msg.obj=Arrays.toString(arr);
Bundle data = new Bundle();
data.putStringArray("str", arr);
msg.setData(data);
// msg.sendToTarget();
//return Arrays.toString(arr);
}
handler.sendMessage(msg);
}
}).start();
}
5592
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
