一个简单的微信点单服务号的数据库的增删改查和Jsonobject,JsonaArray的使用 。
代码和数据库表如下
package wechat;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.util.Date;
import java.sql.*;
import java.text.SimpleDateFormat;
/**
*
* 测试数据库
*/
public class JDBCDAO {
//mysql驱动包名
private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
//数据库连接地址
private static final String URL = "jdbc:mysql://localhost:3306/wxapp";
//用户名
private static final String USER_NAME = "root";
//密码
private static final String PASSWORD = "123456";
private static Connection connection;
public static void main(String[] args) throws SQLException {
/*ResultSet rs=show_shopping();
if(!rs.next()){
System.out.println("没有此商品");
}
rs.previous();
while (rs.next()) { //next（）获取里面的内容
if(rs.getString("Goods").equals(""))
System.out.println("没有此商品");
else
System.out.println(rs.getString("Goods")+" "+rs.getString("Inventory")+" "+rs.getString("Sales")+" "+rs.getString("Price")+" "+rs.getString("Describes")+" ");
}*/
//connection.close();
//rs.close();
JSONObject demo1=new JSONObject();
JSONObject demo2=new JSONObject();
demo1.put("Goods","1");
demo1.put("Amount","1");
demo1.put("Price","18");
demo1.put("Openid","1234");
demo1.put("Describes","xxxxxxxx");
demo2.put("Goods","2");
demo2.put("Amount","2");
demo2.put("Price","20");
demo2.put("Openid","1234");
demo2.put("Describes","xxxxxxxx");
JSONArray re=new JSONArray();
re.add(demo1);
re.add(demo2);
insert_basket(re);
//add_or_reduce_basket("1",0);
//add_or_reduce_basket("3",5,5,"2");
//change_now_to_history("2");
}
public static void insert_person(JSONObject person){//参数个人信息json数组*****测试成功
connection = null;
try {
//加载mysql的驱动类
Class.forName(DRIVER_NAME);
//获取数据库连接
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
//mysql查询语句
String sql = "insert into person(nickname,openid,sex,headimgurl) value (?,?,?,?)";
//Statement对象
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1,person.getString("nickname"));
ps.setString(2,person.getString("openid"));
ps.setString(3,person.getString("sex"));
ps.setString(4,person.getString("headimgurl"));
int row=ps.executeUpdate();
if(row>0){
System.out.println("数据库操作成功");
}else{
System.out.println("数据库操作失败");
}
ps.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public JSONObject find_person(String openid) {//参数为个人微信号*****测试成功
connection = null;
JSONObject re = new JSONObject();
try {
//加载mysql的驱动类
Class.forName(DRIVER_NAME);
//获取数据库连接
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
//mysql查询语句
String sql = "select *from person";
//Statement对象
Statement state = connection.createStatement();//容器
ResultSet rs = state.executeQuery(sql); //将sql语句传至数据库，返回的值为一个字符集用一个变量接收
while (rs.next()) { //next（）获取里面的内容
if (openid == rs.getString("openid")) {
re.put("nickname", rs.getString("nickname"));
re.put("openid", rs.getString("openid"));
re.put("sex", rs.getString("sex"));
re.put("headimgurl", rs.getString("headimgurl"));
System.out.println("获取成功");
break;
} else {
continue;
}
}
state.close();
rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return re;
}
public static ResultSet find_shopping(String goods) {//搜索栏功能***参数为商品名*****测试成功
connection = null;
JSONObject re = new JSONObject();
ResultSet rs1=null;
try {
//加载mysql的驱动类
Class.forName(DRIVER_NAME);
//获取数据库连接
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
//mysql查询语句
String sql = "select *from shopping where Goods like '%"+goods+"%'";
//Statement对象
Statement state = connection.createStatement();//容器
ResultSet rs = state.executeQuery(sql); //将sql语句传至数据库，返回的值为一个字符集用一个变量接收
rs1=rs;
/*while (rs.next()) { //next（）获取里面的内容
re.put("Goods", rs.getString("Goods"));
re.put("Inventory", rs.getString("Inventory"));
re.put("Sales", rs.getString("Sales"));
re.put("Price", rs.getString("Price"));
re.put("Describe", rs.getString("Describes"));
System.out.println(rs.getString("Goods")+" "+rs.getString("Inventory")+" "+rs.getInt("Sales")+" "+rs.getString("Price")+" "+rs.getString("Describes")+" ");
}*/
// state.close();
// rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
/*try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}*/
}
}
return rs1;
}
public static ResultSet show_shopping() {//搜索所有商品*****测试成功
connection = null;
JSONObject re = new JSONObject();
ResultSet rs1 = null;
try {
//加载mysql的驱动类
Class.forName(DRIVER_NAME);
//获取数据库连接
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
//mysql查询语句
String sql = "select *from shopping";
//Statement对象
Statement state = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);//容器
ResultSet rs =state.executeQuery(sql); //将sql语句传至数据库，返回的值为一个字符集用一个变量接收
rs1 = rs;
// state.close();
// rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
/*try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}*/
}
}
return rs1;
}
public void add_shopping(String good,String inventory,int sales,int price,String describes){ //添加商品
connection=null;
try{
Class.forName(DRIVER_NAME);
connection=DriverManager.getConnection(URL,USER_NAME,PASSWORD);
String sql="insert into shopping(Goods,Inventory,Sales,Price,Describes) value (?,?,?,?,?)";
PreparedStatement pst=connection.prepareStatement(sql);
pst.setString(1,good);
pst.setString(2,inventory);
pst.setInt(3,sales);
pst.setInt(4,price);
pst.setString(5,describes);
pst.executeUpdate(sql);
pst.close();
}catch (Exception e){
e.printStackTrace();
}finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
private static void change_shopping(String good,int amount) {
connection=null;
try{
Class.forName(DRIVER_NAME);
connection=DriverManager.getConnection(URL,USER_NAME,PASSWORD);
String sql="update shopping set Sales=Sales+"+amount+",Inventory=Inventory-"+amount+" where Goods='"+good+"'";
PreparedStatement pst=connection.prepareStatement(sql);
pst.executeUpdate(sql);
pst.close();
}catch (Exception e){
e.printStackTrace();
}finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void insert_basket(JSONArray goods){//参数为商品信息json对象，含Goods,Amount,Price,Openid,Describes //****测试成功
connection = null;
System.out.println("goods="+goods);
for(int i=0;i<goods.size();i++) {
JSONObject good = (JSONObject) goods.get(i);
System.out.println("good="+good);
try {
//加载mysql的驱动类
Class.forName(DRIVER_NAME);
//获取数据库连接
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
//mysql插入
String sql = "insert into basket(Goods,Amount,Price,Openid,Describes,Times) value (?,?,?,?,?,?)";
String sql_find = "select * from basket where Goods='" + good.getString("Goods")+"' and openid='"+good.getString("Openid")+"'";
System.out.println("sql_find="+sql_find);
System.out.println("sql="+sql);
//Statement对象
Statement st = connection.createStatement();
PreparedStatement ps = connection.prepareStatement(sql); sql
ResultSet rs = st.executeQuery(sql_find);sql_find
if (!rs.next()) {
ps.setString(1, good.getString("Goods"));
ps.setString(2, good.getString("Amount"));
ps.setString(3, good.getString("Price"));
ps.setString(4, good.getString("Openid"));
ps.setString(5, good.getString("Describes"));
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
ps.setString(6, df.format(new Date()));
int row = ps.executeUpdate();
if (row > 0) {
System.out.println("数据库插入操作成功");
} else {
System.out.println("数据库插入操作失败");
}
} else {
//System.out.println("执行到else***");
int amount=good.getInteger("Amount") + rs.getInt("Amount");
String sql_add = "update basket set Amount=" + amount + " " + "where Goods='" + good.getString("Goods")+"' and openid='"+good.getString("Openid")+"'";
//加个数量
System.out.println("sql_add="+sql_add);
ps = connection.prepareStatement(sql_add);
int row = ps.executeUpdate();
if (row > 0) {
System.out.println("数据库修改操作成功");
} else {
System.out.println("数据库修改操作失败");
}
}
ps.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
public static void add_or_reduce_basket(String good, int amount,int price,String openid){//参数为商品名和商品数量,商品单价//****测试成功
connection=null;
String sql=null;
String sql1=null;
if(amount<=0){
sql="delete from basket where Goods='"+good+"'"+"and openid='"+openid+"'";
}
else{
sql="update basket set Amount="+amount+" where Goods='"+good+"'"+"and openid='"+openid+"'";
sql1="update basket set Price="+amount*price+" where Goods='"+good+"'"+"and openid='"+openid+"'";
}
try{
Class.forName(DRIVER_NAME);
connection=DriverManager.getConnection(URL,USER_NAME,PASSWORD);
System.out.println("sql="+sql);
Statement st=connection.createStatement();
st.executeUpdate(sql);
st.executeUpdate(sql1);
st.close();
}catch (Exception e){
e.printStackTrace();
}finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public ResultSet show_basket(String openid){//记得关连接
connection=null;
ResultSet rs=null;
try {
Class.forName(DRIVER_NAME);
connection=DriverManager.getConnection(URL,USER_NAME,PASSWORD);
String sql="select * from basket"+" where openid='"+openid+"'";
Statement st=connection.createStatement();
rs=st.executeQuery(sql);
}catch (Exception e){
e.printStackTrace();
}finally {
}
return rs;
}
public ResultSet show_now(String openid){//记得关连接
connection=null;
ResultSet rs=null;
try {
Class.forName(DRIVER_NAME);
connection=DriverManager.getConnection(URL,USER_NAME,PASSWORD);
String sql="select * from order_now"+" where openid='"+openid+"'";
Statement st=connection.createStatement();
rs=st.executeQuery(sql);
}catch (Exception e){
e.printStackTrace();
}finally {
}
return rs;
}
public ResultSet show_history(String openid){//记得关连接
connection=null;
ResultSet rs=null;
try {
Class.forName(DRIVER_NAME);
connection=DriverManager.getConnection(URL,USER_NAME,PASSWORD);
String sql="select * from order history"+" where openid='"+openid+"'";
Statement st=connection.createStatement();
rs=st.executeQuery(sql);
}catch (Exception e){
e.printStackTrace();
}finally {
}
return rs;
}
public static void change_basket_to_order_now(String openid){ //*****测试成功
connection=null;
try {
//加载mysql的驱动类
Class.forName(DRIVER_NAME);
//获取数据库连接
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
//mysql插入
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
String sql_time="update basket set Times='"+df.format(new Date())+"' where openid='"+openid+"'"; //购买时间
String sql = "insert into order_now select * from basket where openid='"+openid+"'"; //换位置
String sql_delt="delete from basket where openid='"+openid+"'"; //清空购物车
//需要改变销量以及库存 **获取商品名和数量参数 历史订单里面改
/*String sql_parameter_get="select Goods,"
change_shopping(good,amount);*/
//Statement对象
PreparedStatement ps= connection.prepareStatement(sql_time);
int row_time=ps.executeUpdate();
if(row_time>0)
System.out.println("操作成功");
else
System.out.println("操作失败");
int row=ps.executeUpdate(sql);
if(row>0)
System.out.println("操作成功");
else
System.out.println("操作失败");
int row_delt=ps.executeUpdate(sql_delt);
if(row_delt>0)
System.out.println("操作成功");
else
System.out.println("操作失败");
ps.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void change_now_to_history(String openid){//订单信息，包括时间 //**测试成功
connection=null;
try {
//加载mysql的驱动类
Class.forName(DRIVER_NAME);
//获取数据库连接
connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
String sql = "insert into order_history select * from order_now where openid='"+openid+"'";
String sql_delt="delete from order_now where openid='"+openid+"'";
//Statement对象
String sql_parameter_get="select Goods,Amount from order_now where openid='"+openid+"'";
PreparedStatement ps= connection.prepareStatement(sql); //复制
ps.executeUpdate();
ResultSet rs=ps.executeQuery(sql_parameter_get);
while(rs.next()){
String good=rs.getString("Goods");
int amount=rs.getInt("Amount");
change_shopping(good,amount);
}
ps.executeUpdate(sql_delt);//删除
ps.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}购物车：
历史订单
订单：
个人信息：
商品信息：