MySQL数据库设计好友列表遇到的问题

2015年3月22日

/**、

该程序预期目标:

通过访问数据库woshishutong中的数据表文件friendlist（即好友表文件）

通过查找与account关联的buddyaccount

然后借助buddyaccount访问数据表文件verification（即用户表文件），获得好友的详细信息如昵称（nick）、头像（avatar）等信息；

*/

/**

实际测试结果：(bug所在)

1.可能存在的中文信息乱码问题   当输入为qwer时，可获得所有与之关联的好友账号，好友详细信息也较完整，但是与之相关的中文名称的好友的详细信息没有输出；

Qwer

-----------好友--------asd

-----------好友--------qq

-----------好友--------你好

-----------好友--------在水一方

-------rs!=null--------true

-------rs.next()--------true

---------------------好友详细信息---------------

------resultSet.getString account:--------asd

------resultSet.getString nick:--------不想长大

------resultSet.getString trends:--------null

---------------------好友详细信息---------------

------resultSet.getString account:--------qq

------resultSet.getString nick:--------茉莉花

------resultSet.getString trends:--------null

2但是当输入为abc  或者qq 时仅仅输出与输入相关联的好友账号，无任何好友列表输出；

如当输入为abc时：

abc

-----------好友--------qwer

-----------好友--------asd

-----------好友--------qq

-------rs!=null--------true

-------rs.next()--------true

如当输入为qq时：

 

qq

-----------好友--------qwer

-----------好友--------asd

-----------好友--------abc

-------rs!=null--------true

-------rs.next()--------true

*/

 

 

 

 

 

 

另外附上数据库表信息及测试所用代码

Table   verification

# account, password, nick, avatar, trends,sex, age, lev

'abc', '123456', '风', NULL, '风华岁月 我们定义未来', '男', '18', '23'

'asd', '123456', '不想长大', NULL, NULL, NULL, NULL, NULL

'qq', '123456', '茉莉花', NULL, NULL, NULL, NULL, NULL

'qwer', '123456', '浪客剑心', NULL, '天空下起了大雨，淋湿了俄的思绪', '女', '19', '25'

'sunny冰青', '123456', '如你一般的人', NULL, '最怕和在乎的人慢慢变远的过程，真的是发自内心的疼。', '男', '22', '35'

'zxc', '123456', '年轻就好', NULL, '奋斗吧！少年', '男', '23', '35'

'你好', '123456', '不说年少', NULL, '如果没有你，没有过去我不会伤心', '女', '32', '28'

'在水一方', '123456', '空心菜', NULL, '人生若只如初见，何必再见。', '女', '18', '29'

 

Table   friendlist

 

# hostaccount, buddyaccount, number

'qwer', 'asd', '1'

'qwer', 'qq', '2'

'qwer', '你好', '3'

'qwer', '在水一方', '4'

'abc', 'qwer', '5'

'abc', 'asd', '6'

'abc', 'qq', '7'

'qq', 'qwer', '8'

'qq', 'asd', '9'

'qq', 'abc', '10'

'zxc', 'qwer', '11'

'zxc', 'asd', '12'

'zxc', 'qq', '13'

 

 

 

 

 

 

 

 

 

 

测试所用程序相关代码

 

 

 

 

主类  MySQLTest.java文件

package demo;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Scanner;

 

public classMySQLTest

{

    public static void main(String[] args)

    {

       Scannerscanner = newScanner(System.in);

       Stringaccount = scanner.nextLine();

       getBuddyList(account);

    }

    public staticArrayList<HashMap<String,Object>> getBuddyList(String account)

    {

        int i = 0;

        ArrayList<String> list = newArrayList<String>();

       ArrayList<HashMap<String,Object>> resultlist = newArrayList<HashMap<String,Object>>();

        String[] array = null;

       Connectionconnection = null;

       try

       {  

           Stringsql = "select * from friendlist wherehostaccount = ?";

           connection= DatabaseUtils.getDatabaseUtils().getConnection();

           PreparedStatementpreStatement = connection.prepareStatement(sql);

           preStatement.setString(1,account);

           ResultSetresultSet = preStatement.executeQuery();

           while(resultSet.next())

           {

             

                  list.add(resultSet.getString("buddyaccount"));

             

           }

           int size=list.size(); 

            array = (String[])list.toArray(new String[size]);

            for(int j=0;j<array.length;j++)

           { 

                System.out.println("-----------好友--------"+array[j]); 

            } 

       }

       catch (ClassNotFoundExceptione)

       {

          

           e.printStackTrace();

       }

       catch (SQLException e)

       {

          

           e.printStackTrace();

       };

      

       try

       {

           DatabaseUtils.getDatabaseUtils().closeConnection(connection);

       }

       catch (SQLException e)

       {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

      

      

 

      

      

      

      

       try

       {  

           Stringql = "select * from verification";

           connection= DatabaseUtils.getDatabaseUtils().getConnection();

           PreparedStatementpre = connection.prepareStatement(ql);

           ResultSetrs = pre.executeQuery();

           System.out.println("-------rs!=null--------"+(rs!=null));

           System.out.println("-------rs.next()--------"+(rs.next()));

           //String[] s = new String[]{"abc","asd","qq","qwer"};

           while(rs.next()&&i<array.length)

           {

              if(rs.getString("account").equals(array[i]))

              {

                  System.out.println("---------------------好友详细信息---------------");

                  System.out.println("------resultSet.getString account :--------"+(rs.getString("account")));

                  System.out.println("------resultSet.getString nick :--------"+(rs.getString(3)));

                  System.out.println("------resultSet.getString trends :--------"+(rs.getString(5)));

                  HashMap<String,Object>map = newHashMap<String,Object>();

                  map.put("account",rs.getString("account"));

                  map.put("nick", rs.getString("nick"));

                  map.put("avatar",rs.getInt("avatar"));

                  map.put("trends",rs.getString("trends"));

                  map.put("sex", rs.getString("sex"));

                  map.put("age", rs.getInt("age"));

                  map.put("lev", rs.getInt("lev"));

                  resultlist.add(map);

              }

              i++;

           }

       }

       catch (ClassNotFoundExceptione)

       {

          

           e.printStackTrace();

       }

       catch (SQLException e)

       {

          

           e.printStackTrace();

       };

      

       try

       {

           DatabaseUtils.getDatabaseUtils().closeConnection(connection);

       }

       catch (SQLException e)

       {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

       return resultlist;

   

          

    }

}

 

 

DatabaseUtils.java 文件

/**

数据库连接和关闭

*/

package demo;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

 

 

public class DatabaseUtils

{

       privatestatic DatabaseUtils dbUtils;

       privateDatabaseUtils()

       {

             

       }

       publicsynchronized static DatabaseUtils getDatabaseUtils()

       {

              if(dbUtils==null)

              {

                     dbUtils= new DatabaseUtils();

              }

             

              returndbUtils;

       }

      

       publicConnection getConnection() throws ClassNotFoundException,SQLException

       {

             

                     Class.forName("com.mysql.jdbc.Driver");         

                     Stringurl = "jdbc:mysql://localhost:3306/woshishutong";

                     Stringusername = "root";

                     Stringpassword = "930126";

                     Connectionconnection = DriverManager.getConnection(url,username,password);

                     returnconnection;

             

       }

       publicvoid closeConnection(Connection connection) throws SQLException

       {

              if(connection!=null)

              {

                           

                     connection.close();

                    

              }

       }