京东Alpha平台开发笔记系列（三）
摘要：通过前面两篇文章的讲述，大致了解了JdAlpha平台前端开发的主要流程。接下来本篇文章主要讲述后台服务器端开发的主要流程。这里会涉及到后台服务器的搭建的内容，本篇文章就不以赘述，如需了解请读下面一篇文章。
WEB服务器搭建（Apache+Tomcat+eclipse）
首先献上代码：JdAlpha应用（佳宝服务）服务器端代码
有积分的支持一下，没有的也没关系我已将代码上传到（github代码库）。
本人学生一枚，希望可以多多与各位交流一下技术问题！
1、通讯机制
京东Alpha应用与后台服务器通讯的方式为发送post请求，所以后台服务器只需要能接收post请求即可。
默认大家已经搭建好服务器端，这里我是通过建立一个Web工程，通过servlet方式接收post请求，并把
请求数据进行解析处理。处理完成后返回需要播报的信息。
2、设计流程
建立web工程
配置.jsp/.xml文件
建立servlet逻辑
建立主处理文件.java文件
3、设计实践
建立工程与工程文件的配置这里均不赘述，这里直接从工程文件的进行讲解。
工程文件目录：
(1)web.xml文件配置
<servlet> <servlet-name>Price</servlet-name> <servlet-class>com.yxtt.hold.PriceSevlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>Price</servlet-name> <url-pattern>/price</url-pattern> </servlet-mapping>
这里为servlet进行绑定接收处理的java类（即代码中的com.yxtt.hold.PriceSevlet），当有访问时直接将请求数据交给PriceServlet类进行个性化处理。
（2）PriceServlet类解析
首先上代码：
import java.io.BufferedReader; import java.io.IOException; import java.io.PrintWriter; import java.nio.charset.StandardCharsets; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.tomcat.util.codec.binary.Base64;
public class PriceSevlet extends HttpServlet { public PriceSevlet() { super(); } public void destroy() { super.destroy(); // Just puts "destroy" string in log } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); request.getCharacterEncoding(); request.setCharacterEncoding(request.getCharacterEncoding()) ; response.setCharacterEncoding("utf-8"); String param = getBodyData(request); if (Base64.isBase64(param)) { param = new String(Base64.decodeBase64(param), StandardCharsets.UTF_8); } System.out.println("param:" + param); PrintWriter out = response.getWriter(); //反馈 out.write(DataProcess.dataprocess(param)); //DataProcess类为功能处理函数 out.flush(); out.close(); } //格式化请求数据 public void init() throws ServletException { } //获取请求体中的字符串(POST) private static String getBodyData(HttpServletRequest request) { StringBuffer data = new StringBuffer(); String line = null; BufferedReader reader = null; try { reader = request.getReader(); while (null != (line = reader.readLine())) data.append(line); } catch (IOException e) { } finally { } return data.toString(); } }
此类为请求数据接收函数，将请求数据格式化，然后通过DataProcess类将格式化后的数据进行后续的数据分析、数据处理、数据反馈，反馈数据得出后，传入out.write()方法，即可进行输出反馈。
（3）DataProcess类解析
import org.eclipse.jdt.internal.compiler.batch.Main; import net.sf.json.JSONObject; public class DataProcess { static String reback = null, welcome = "欢迎使用佳宝服务，你可以对我说：菜价、记账、或者查账单，若想离开请对我说，退出", errorRequest = "我不明白你想做什么，你可以说报菜价、记账、查账单等"; static boolean shouldEndSession = false; static String priceTable="{\"vegetables\": {\"西红柿\": \"1.99\",\"黄瓜\": \"2.59\",\"尖椒\": \"1.99\", " + "\"土豆\": \"1.29\", " + "\"茄子\": \"1.59\", " + "\"白菜\": \"1.29\", " + "\"芹菜\": \"3.49\", " + "\"菜花\": \"3.49\", " + "\"蒜薹\": \"4.99\", " + "\"胡萝卜\": \"1.19\", " + "\"大葱\": \"2.29\", " + "\"甘蓝\": \"1.29\" " + "}, " + "\"milk\": { " + "\"牛奶\": \"2.25\", " + "\"豆奶\": \"1.25\", " + "\"花生奶\": \"1.59\" " + "}, " + "\"meat\": { " + "\"五花肉\": \"9.90\", " + "\"牛肉\": \"39.9\", " + "\"鸡肉\": \"9.9\", " + "\"鸡蛋\": \"3.79\", " + "\"猪肉\": \"11.9\"," + "\"排骨\": \"15.9\"" + "}," + "\"fruits\": {" + "\"苹果\": \"5.99\"," + "\"梨\": \"2.59\"," + "\"柑橘\": \"5.59\"," + "\"葡萄\": \"7.99\"," + "\"香蕉\": \"2.99\"" + "}" + "}"; public static String dataprocess(String param) { String requestType, requestIsNew; requestType = extractionValue(param, "request","type", "", 2); requestIsNew = extractionValue(param, "session","isNew", "", 2); if(requestType.equals("IntentRequest")) { switch(extractionValue(param, "request","intent", "name", 3)) { case "Add.Alpha.CancelIntent": reback = welcome; break; case "Alpha.HelpIntent": reback = "欢迎使用佳宝服务的帮助，佳宝服务是一款致力于服务型的语音应用，在这里你可以查询菜价，记账单，查账单等操作，赶快试一试吧！"; break; case "Alpha.CancelIntent": reback = "已退出，期待您下次使用，佳宝再见!"; break; case "MainQuery": reback = MainQuery.MainQuery(extractionValue(param, "request","intent", "slots", 3)); break; case "Effect": reback = EffectFun(extractionValue(param, "request","intent", "slots", 3),param); System.out.println(reback); break; case "CountQuery": reback = CountQuery.CountQuery(extractionValue(param, "session","user", "userId", 3),extractionValue(param, "request","intent", "slots", 3)); break; case "WriteCount": reback = WriteCount.WriteCount(extractionValue(param, "request","intent", "slots", 3),extractionValue(param, "session","user", "userId", 3)); break; } System.out.println(extractionValue(param, "request","intent", "name", 3)); }else if(requestType.equals("LaunchRequest")){ if(requestIsNew.equals("true")) reback = welcome; else reback = errorRequest; } String backinfo = "{\"contexts\":{},\"directives\":[],\"response\":{\"output\":{\"type\":\"PlainText\",\"text\":\""+reback+"\"}},\"shouldEndSession\":"+shouldEndSession+",\"version\":\"1.0\"}"; return backinfo; } private static String EffectFun(String slots, String param) { String effectName = extractionValue(slots, "myEffect", "value", "", 2); System.out.println(effectName); if(effectName.equals("我的账单")) { return CountQuery.CountQuery(extractionValue(param, "session","user", "userId", 3),"null");//先查询数据库然后进行反馈 }else if(effectName.equals("我要记账")) { return "请您说今天干什么花了多少钱，如果不知道记账规则请说账本规则。"; }else if(effectName.equals("报菜价")) { return "西红柿1.99元一斤，黄瓜2.59元一斤，土豆1.29元一斤等，你可以直接问某种菜品的价格，如白菜的价格，所有菜价均来源于网络收集，因各地有所差异，这里仅供参考，具体以当地为准！"; }else if(effectName.equals("不用了")) { return "好的，期待您下次使用！再见！"; }else if(effectName.equals("账本规则")) { return "我们将消费类型归纳为五类：娱乐、学习、衣着、出行、食宿，你可以说今天娱乐花了40元"; }else { return errorRequest; } //return extractionValue; } public static String extractionValue(String obj, String key1, String key2, String key3, int layer){ String outData = null; //try { if(layer==1){ JSONObject jsonObject = new JSONObject().fromObject(obj); Object data=jsonObject.get(key1); outData = data.toString(); } else if (layer == 2){ JSONObject jsonObject = JSONObject.fromObject(obj); Object data=jsonObject.get(key1); jsonObject = JSONObject.fromObject(data.toString()); data=jsonObject.get(key2); outData = data.toString(); } else if (layer == 3){ JSONObject jsonObject = new JSONObject().fromObject(obj); Object data=jsonObject.get(key1); jsonObject = new JSONObject().fromObject(data.toString()); data=jsonObject.get(key2); jsonObject = new JSONObject().fromObject(data.toString()); data=jsonObject.get(key3); outData = data.toString(); } return outData; } }
这里返回的数据简单粗暴，中文反馈，叮咚音箱可以直接对反馈的文字进行读取。
此类添加了一个json类，import net.sf.json.JSONObject;需要读者自行下载添加。
首先，dataprocess（）方法接收到格式化的字符串后（字符串均为json数据格式），通过json数据处理函数获取功能头的名称，接着按照不同的功能进行以下流程。
如：extractionValue(param, "request","intent", "name", 3)，通过本句程序的调用，直接可以得到功能的名称：
"Add.Alpha.CancelIntent" //JD官方取消意图
"Alpha.HelpIntent" //JD官方帮助意图
"Alpha.CancelIntent" //JD官方取消意图（与第一个的区别可参考JD文档）
"MainQuery" //自定义价格广播功能
"Effect" //自定义功能汇总接口
"CountQuery" //自定义价格询问功能
"WriteCount" //自定义记账功能
通过以上功能分类机制将 不同的功能的处理反馈分类处理即可完成整体的功能部署。
（4）其他各功能类（这里直接上代码，不进行阐述，都很简单）
Count.java
package com.yxtt.hold; public class Count { String entertainmentCount, studyCount, clothCount, travelCount, eatCount, sumCount; public String getEntainmentCount() { return entertainmentCount; } public void setEntainmentCount(String entertainmentCount) { this.entertainmentCount = entertainmentCount; } public String getStudyCount() { return studyCount; } public void setStudyCount(String studyCount) { this.studyCount = studyCount; } public String getClothCount() { return clothCount; } public void setClothCount(String clothCount) { this.clothCount = clothCount; } public String getTravelCount() { return travelCount; } public void setTravelCount(String travelCount) { this.travelCount = travelCount; } public String getEatCount() { return eatCount; } public void setEatCount(String eatCount) { this.eatCount = eatCount; } public String getSumCount() { return sumCount; } public void setSumCount(String sumCount) { this.sumCount = sumCount; } }
MainQuery.java
public class MainQuery { public static String MainQuery(String slots) { String meatMatch,vegetableMatch,milkMatch,meatF,vegetableF,milkF,reBack=null; meatMatch = DataProcess.extractionValue(slots,"Meats","matched","",2); vegetableMatch = DataProcess.extractionValue(slots,"Vegetable","matched","",2); milkMatch = DataProcess.extractionValue(slots,"Milk","matched","",2); if(meatMatch.equals("true")) { meatF = DataProcess.extractionValue(slots,"Meats","value","",2); reBack = meatF +"的单价为"+DataBaseCon.queryOne("meatprice", "price", meatF, "name")+"元！"; }else if(vegetableMatch.equals("true")) { vegetableF = DataProcess.extractionValue(slots,"Vegetable","value","",2); reBack = vegetableF +"的单价为"+DataBaseCon.queryOne("vegetableprice", "price", vegetableF, "name")+"元"; }else if(milkMatch.equals("true")) { milkF = DataProcess.extractionValue(slots,"Milk","value","",2); reBack = milkF +"的单价为"+DataBaseCon.queryOne("milkprice", "price", milkF, "name")+"元！"; } if(meatMatch.equals("false")&&vegetableMatch.equals("false")&&milkMatch.equals("false")) { /*meatF = DataProcess.extractionValue(slots,"Meats","value","",2); if(meatF!="Meats") { reBack = "您所查询的"+meatF+"还未收录，请换一个再问。"; } vegetableF = DataProcess.extractionValue(slots,"Vegetable","value","",2); if(vegetableF!="Vegetable") { reBack = "您所查询的"+vegetableF+"还未收录，请换一个再问。"; }*/ milkF = DataProcess.extractionValue(slots,"Milk","value","",2); if(milkF!="Milk") { reBack = "抱歉，您所查询的"+milkF+"还未收录，我们会尽快收录。"; } } return reBack; } }
CountQuery.java
public class MainQuery { public static String MainQuery(String slots) { String meatMatch,vegetableMatch,milkMatch,meatF,vegetableF,milkF,reBack=null; meatMatch = DataProcess.extractionValue(slots,"Meats","matched","",2); vegetableMatch = DataProcess.extractionValue(slots,"Vegetable","matched","",2); milkMatch = DataProcess.extractionValue(slots,"Milk","matched","",2); if(meatMatch.equals("true")) { meatF = DataProcess.extractionValue(slots,"Meats","value","",2); reBack = meatF +"的单价为"+DataBaseCon.queryOne("meatprice", "price", meatF, "name")+"元！"; }else if(vegetableMatch.equals("true")) { vegetableF = DataProcess.extractionValue(slots,"Vegetable","value","",2); reBack = vegetableF +"的单价为"+DataBaseCon.queryOne("vegetableprice", "price", vegetableF, "name")+"元"; }else if(milkMatch.equals("true")) { milkF = DataProcess.extractionValue(slots,"Milk","value","",2); reBack = milkF +"的单价为"+DataBaseCon.queryOne("milkprice", "price", milkF, "name")+"元！"; } if(meatMatch.equals("false")&&vegetableMatch.equals("false")&&milkMatch.equals("false")) { /*meatF = DataProcess.extractionValue(slots,"Meats","value","",2); if(meatF!="Meats") { reBack = "您所查询的"+meatF+"还未收录，请换一个再问。"; } vegetableF = DataProcess.extractionValue(slots,"Vegetable","value","",2); if(vegetableF!="Vegetable") { reBack = "您所查询的"+vegetableF+"还未收录，请换一个再问。"; }*/ milkF = DataProcess.extractionValue(slots,"Milk","value","",2); if(milkF!="Milk") { reBack = "抱歉，您所查询的"+milkF+"还未收录，我们会尽快收录。"; } } return reBack; } }
WriteCount.java
import java.util.Calendar; public class WriteCount { public static String WriteCount(String slots, String userID) { String days,countType, userId; int money; if(DataProcess.extractionValue(slots, "countType","matched", "", 2).equals("false")) { return "暂时不支持记录这个分类，我们将消费类型归纳为五类：娱乐、学习、衣着、出行、食宿，你可以说今天娱乐花了40元。"; }else if(DataProcess.extractionValue(slots, "Days","matched", "", 2).equals("false")&&DataProcess.extractionValue(slots, "countType","matched", "", 2).equals("true")){ countType = DataProcess.extractionValue(slots, "countType","value", "", 2); money = Integer.valueOf(DataProcess.extractionValue(slots, "number","value", "", 2)); userId = userID.substring(userID.indexOf(".",userID.indexOf(".")+1 )+1); System.out.println("days:null"+",countType:"+countType+",money:"+money+"."); //System.out.println(userID.substring(userID.indexOf(".",userID.indexOf(".")+1)+1)); //System.out.println(DataBaseCon.queryUserID(userID.substring(userID.indexOf(".",userID.indexOf(".")+1 )+1))); // 获取当前年份、月份、日期 Calendar cale = null; String thisDate, countDate;//当前日期与记账日期 cale = Calendar.getInstance(); int year = cale.get(Calendar.YEAR); int month = cale.get(Calendar.MONTH) + 1; int day = cale.get(Calendar.DATE); if(month<10) { thisDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) thisDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-0"+String.valueOf(day); }else { thisDate = String.valueOf(year)+"-"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) thisDate = String.valueOf(year)+"-"+String.valueOf(month)+"-0"+String.valueOf(day); } if(month<10) { countDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) countDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-0"+String.valueOf(day); }else { countDate = String.valueOf(year)+"-"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) countDate = String.valueOf(year)+"-"+String.valueOf(month)+"-0"+String.valueOf(day); } switch (countType) { case "娱乐": countType = "entertainment"; break; case "学习": countType = "study"; break; case "衣着": countType = "cloth"; break; case "出行": countType = "travel"; break; case "食宿": countType = "eat"; break; } if(DataBaseCon.queryUserID(userId)) { DataBaseCon.insertCount(countDate, countType, money, thisDate, userId); }else { if(DataBaseCon.insertData("userlist", "userId", userId)) { System.out.println("用户信息插入成功！下面即将创建用户表。。。"); if(DataBaseCon.newTable(userId)) System.out.println("用户表创建完成，下面插入数据。。。。"); DataBaseCon.insertCount(countDate, countType, money, thisDate, userId); } } return "已为您记录到账单。"; }else { days = DataProcess.extractionValue(slots, "Days","value", "", 2); countType = DataProcess.extractionValue(slots, "countType","value", "", 2); money = Integer.valueOf(DataProcess.extractionValue(slots, "number","value", "", 2)); userId = userID.substring(userID.indexOf(".",userID.indexOf(".")+1 )+1); System.out.println("days:"+days+",countType:"+countType+",money:"+money+"."); //System.out.println(userID.substring(userID.indexOf(".",userID.indexOf(".")+1)+1)); //System.out.println(DataBaseCon.queryUserID(userID.substring(userID.indexOf(".",userID.indexOf(".")+1 )+1))); // 获取当前年份、月份、日期 Calendar cale = null; String thisDate, countDate;//当前日期与记账日期 cale = Calendar.getInstance(); int year = cale.get(Calendar.YEAR); int month = cale.get(Calendar.MONTH) + 1; int day = cale.get(Calendar.DATE); if(month<10) { thisDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) thisDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-0"+String.valueOf(day); }else { thisDate = String.valueOf(year)+"-"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) thisDate = String.valueOf(year)+"-"+String.valueOf(month)+"-0"+String.valueOf(day); } //词语分析 switch(days) { case "前天": day = day-2; break; case "昨天": day = day-1; break; case "今天": break; } if(month<10) { countDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) countDate = String.valueOf(year)+"-0"+String.valueOf(month)+"-0"+String.valueOf(day); }else { countDate = String.valueOf(year)+"-"+String.valueOf(month)+"-"+String.valueOf(day); if(day<10) countDate = String.valueOf(year)+"-"+String.valueOf(month)+"-0"+String.valueOf(day); } switch (countType) { case "娱乐": countType = "entertainment"; break; case "学习": countType = "study"; break; case "衣着": countType = "cloth"; break; case "出行": countType = "travel"; break; case "食宿": countType = "eat"; break; } if(DataBaseCon.queryUserID(userId)) { DataBaseCon.insertCount(countDate, countType, money, thisDate, userId); }else { if(DataBaseCon.insertData("userlist", "userId", userId)) { System.out.println("用户信息插入成功！下面即将创建用户表。。。"); if(DataBaseCon.newTable(userId)) System.out.println("用户表创建完成，下面插入数据。。。。"); DataBaseCon.insertCount(countDate, countType, money, thisDate, userId); } } return "已为您记录到账单。"; } } }
DataBaseCon.java
import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.Statement; public class DataBaseCon { static boolean flag; //声明Connection对象 static Connection con; static //驱动程序名 String driver = "com.mysql.jdbc.Driver"; //URL指向要访问的数据库名mydata static String url = "jdbc:mysql://localhost:3306/jdalpha"; //MySQL配置时的用户名 static String user = "root"; //MySQL配置时的密码 static String password = ""; //遍历查询结果集 public static boolean newTable(String userId) { String sqlStr = "CREATE TABLE "+ userId + "(date Date, entertainment Int(55), study Int(55), cloth Int(55), travel Int(55), eat Int(55))"; return dataSQL(sqlStr); } public static boolean insertCount(String countDate, String countType, int money, String thisDate, String userId) { boolean flag = queryDate(userId, thisDate); if(flag) { if(updateMyCount(userId, countDate, countType, String.valueOf(money))) System.out.println("数据已存储！"); }else { flag = insertData(userId, "date", thisDate); //建立thisData记录 if(flag) System.out.println("已生成date记录！"); if(updateMyCount(userId, countDate, countType, String.valueOf(money))) System.out.println("数据已存储！"); } return flag; } public static Count queryCount(String userId, String date) { Count count = new Count(); String entertainmentCount, studyCount, clothCount, travelCount, eatCount, sumCount; entertainmentCount = queryOne(userId, "entertainment", date, "date"); studyCount = queryOne(userId, "study", date, "date"); clothCount = queryOne(userId, "cloth", date, "date"); travelCount = queryOne(userId, "travel", date, "date"); eatCount = queryOne(userId, "eat", date, "date"); if(entertainmentCount == null) entertainmentCount ="0"; if(studyCount == null) { studyCount = "0"; } if(clothCount == null) { clothCount = "0"; } if(travelCount == null) { travelCount = "0"; } if(eatCount == null) { eatCount = "0"; } int mainMax = Integer.valueOf(entertainmentCount) + Integer.valueOf(studyCount) + Integer.valueOf(clothCount) + Integer.valueOf(travelCount) + Integer.valueOf(eatCount) ; //rebackStr = "今天"+"娱乐花了"+entertainmentCount+"元"+"，学习花了"+studyCount+"元" //+",衣着花了"+clothCount+"元"+"，出行花了"+travelCount+"元"+"，食宿花了"+eatCount+"元,今天一共花了"+mainMax+"元"; sumCount = String.valueOf(mainMax); count.setEntainmentCount(entertainmentCount); count.setStudyCount(studyCount); count.setClothCount(clothCount); count.setTravelCount(travelCount); count.setEatCount(eatCount); count.setSumCount(sumCount); //System.out.println("sumcount:"+count.getSumCount()); return count; } public static boolean queryUserID(String userId) { String sqlStr = "select * from userlist" + " where '" + userId + "'"; boolean flag = false; try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法，连接MySQL数据库！！ con = (Connection) DriverManager.getConnection(url,user,password); //2.创建statement类对象，用来执行SQL语句！！ Statement statement = (Statement) con.createStatement(); //要执行的SQL语句 ResultSet resultSet = statement.executeQuery(sqlStr); while(resultSet.next()) { resultSet.getString("userId"); if(resultSet.getString("userId").equals(userId)) { flag = true; } } con.close(); } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ } return flag; } public static boolean queryDate(String userId, String thisDate) { String sqlStr = "select * from " + userId + " where '" + thisDate + "'"; boolean flag = false; try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法，连接MySQL数据库！！ con = (Connection) DriverManager.getConnection(url,user,password); //2.创建statement类对象，用来执行SQL语句！！ Statement statement = (Statement) con.createStatement(); //要执行的SQL语句 ResultSet resultSet = statement.executeQuery(sqlStr); //当天的日期 //System.out.println("thisdata的值为："+thisDate); while(resultSet.next()) { resultSet.getString("date"); //循环获取的到的日期值 //System.out.println("date的值为："+resultSet.getString("date")); if(resultSet.getString("date").toString().equals(thisDate)) { flag = true; } } con.close(); //标志位的布尔值 //System.out.println("flag的值为："+flag); } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ } return flag; } public static boolean dataSQL(String sqlStr) { try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法，连接MySQL数据库！！ con = (Connection) DriverManager.getConnection(url,user,password); //2.创建statement类对象，用来执行SQL语句！！ Statement statement = (Statement) con.createStatement(); //要执行的SQL语句 flag = statement.execute(sqlStr); con.close(); } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); }catch (Exception e) { e.printStackTrace(); }finally{ } return flag; } @SuppressWarnings("finally") public static boolean insertData(String biao, String ziduan, String valueStr) { String sqlStr = "insert into " + biao + "(" + ziduan + ")" + "values('" + valueStr + "')"; try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法，连接MySQL数据库！！ con = (Connection) DriverManager.getConnection(url,user,password); //2.创建statement类对象，用来执行SQL语句！！ Statement statement = (Statement) con.createStatement(); //要执行的SQL语句 int resultSet = statement.executeUpdate(sqlStr); System.out.println(resultSet); con.close(); } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ return true; } } /* UPDATE Customers SET cust_email = ' kim@qq.com' WHERE cust_id = '10000005';*/ public static boolean updateMyCount(String userId, String countDate, String countType, String money) { String str = "UPDATE " + userId + " SET " + countType + " = '" + money + "' WHERE date = '" + countDate + "';"; int resultSet = 0; try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法，连接MySQL数据库！！ con = (Connection) DriverManager.getConnection(url,user,password); //2.创建statement类对象，用来执行SQL语句！！ Statement statement = (Statement) con.createStatement(); //要执行的SQL语句 resultSet = statement.executeUpdate(str); con.close(); } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ } if(resultSet == 0) return false; else return true; } //---------------------------------------------- //该方法用于查询某一项消费 //---------------------------------------------- @SuppressWarnings("finally") public static String queryOne(String userId, String countType, String countDate, String ziduan) { ResultSet rs; String sqlStr="select * from " + userId + " where "+ziduan+" ='"+countDate+"'"; String balance = null; try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法，连接MySQL数据库！！ con = (Connection) DriverManager.getConnection(url,user,password); //2.创建statement类对象，用来执行SQL语句！！ Statement statement = (Statement) con.createStatement(); //要执行的SQL语句 rs = statement.executeQuery(sqlStr); rs.next(); //指向第一条数据 balance=rs.getString(countType); con.close(); } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ return balance; } } }
以上代码均简单易懂，还有不懂的留言，共同解决。
