java联网小型数据库设计(MESQL)

实现自定义的数据库时的思路及其对应的代码
缓存查询，不同表操作，返回的数据泛型约束，拥有多方面提示使用户有愉悦的体验，多线程接收客户端SQL语句
读写文件部分使用序列化和反序列化更为接近数据库的真实操作并更好对实体类进行操作，使用泛型接口后可对多种实体类进行操作，任意表任意数据的插取 太懒，没做

1.创建学生类

数据库中需要传入的学生类属性id,name,age

package sqldemo.domain;

public class Student {
    private int id ;
    private String name ;
    private int age;

    public Student() {
    }

    public Student(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        if (name != null) {
            this.name = name;
        }
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        if (age!=0){
        this.age = age;
        }
    }

    @Override
    public String toString() {
        return id +"\t"+
                 name +"\t"+
                age ;
    }
}

2.编写客户端类

客户端实现sql语句的编写传给服务端并确保传入的sql语句符合标准

package sqldemo.client;

import sqldemo.utils.Judge;

import java.io.*;
import java.net.Socket;
import java.util.Scanner;
/*
客户端
*/
public class Client {
    private Socket socket;
    private InputStream is;
    private  Scanner scanner = new Scanner(System.in);

    public Client(String addr , int port) {
        try{
        socket= new Socket(addr, port);
            System.out.println("成功连接");
        func();
    } catch (IOException e) {
        e.printStackTrace();
    }
    }
    public void func () throws IOException {
              is = socket.getInputStream();
            BufferedReader br = new BufferedReader(
                    new InputStreamReader(is));
        PrintWriter pw = new PrintWriter(socket.getOutputStream(),true);
        while (true){
//进入方法解析sql语句是否正确
            String sql = scan();
            pw.println(sql);
            if ("end".equals(sql)){
                System.out.println("已成功关闭");
                pw.close();
                br.close();
                socket.close();
                break;
            }

            System.out.println("你输入了：\n"+sql);

            System.out.println("----------------");
            while (true){
                String msg = br.readLine();
                System.out.println(msg);
//当读取到服务端传达的空格字符串时跳出循环，重新输入sql语句
                if (" ".equals(msg)){
                    System.out.println("----------------");
                    break;
                }
            }

            }
        }

        /*
        * 控制台输入并判断SQL语句
        * */
        public String scan(){
            System.out.println("请输入SQL语句：");
            String sql = scanner.nextLine();
//传入end时返回end字符串使其连接关闭
            if ("end".equals(sql)){
                return sql;
            }
//进入方法解析语句是否正确
            Judge judge = new Judge(sql);
            if (!judge.all()){
                System.out.println("语法错误，请重新输入");
                return scan();
            }else {
                return sql;
            }
        }
}

3.编写规范判断sql语句的接口

package sqldemo.dao;


public interface JudgeDao {
    boolean findAll();
    boolean findOne();
    boolean delAll();
    boolean delOne();
    boolean update();
    boolean insert();
}

4.编写判断sql语句的类

实现JudgeDao接口，该类用来判断字符串是否正确或字符串属于哪种语句

package sqldemo.utils;
import sqldemo.dao.JudgeDao;
import sqldemo.domain.Student;

import java.io.File;

public class Judge implements JudgeDao {
    private String temp;   //传入的sql语句
    private String sql;  //规范需要传入的sql语句
    private String[] split; //分割sql字符串
    public Judge(String temp) {
        this.temp = temp;
	//初始化，将传入的sql语句通过空格字符串分割成数组
        split = temp.split(" ");
    }

//判断为哪个语句，分割出输入的表名
    public String fileName(){
            if (findAll() || findOne()) {
                return split[3];
            } else if (delAll() || delOne() || update()) {
                return split[1];
            }
        return null;
    }
//判断是否为增加语句，分割出表名
    public String insertFile(){
        if (insert()){
            return split[2];
        }
        return null;
    }
//分割出输入sql语句的关键数据
    public String stuMem(){
        if (findOne()){
            return split[6];
        }else if (delOne()||insert()){
            return split[4];
        }else if (update()){
            return split[4]+" "+split[7];
        }else {
            return "";
        }
    }
    //判断为哪个语句
    public boolean all(){
        return findAll()||findOne()||delAll()
                ||delOne()||update()||insert();
    }

    @Override
    public boolean findAll() {
         sql = "select * from ?";
        String[] s = sql.split(" ");
        return equalIgSplit(3,s);
    }


    @Override
    public boolean findOne() {
        sql="select * from 文件名/表名 where id= ?";
        String[] s = sql.split(" ");

        return equalSplit(4,6,s)&&
                equalIgSplit(3,s);

    }

    @Override
    public boolean delAll() {
        sql="delete ?";
        String[] s = sql.split(" ");
        return   equalIgSplit(1,s);

    }

    @Override
    public boolean delOne() {
        sql="Delete 表名 where id= ?";
        String[] s = sql.split(" ");

        return  equalIgSplit(1,s)
                &&equalSplit(2,4,s);

    }

    @Override
    public boolean update() {
        sql="update ? set name= ? where id= ?";
        String[] s = sql.split(" ");

        return equalIgSplit(1,s)&&
                equalSplit(2,4,s)&&equalSplit(5,7,s);
    }

    @Override
    public boolean insert() {
        sql="insert into ? values( ?,?,? )";
        String[] s = sql.split(" ");
        return equalIgSplit(2,s)&&equalSplit(3,4,s)
                &&equalSplit(5,6,s);
    }






    //分割后字符串拼接
    public String sp(int n,String[] s){
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < n; i++) {
            sb.append(s[i]);
        }
        return sb.toString();
    }

    public String sp(int start,int end,String[] s){
        StringBuffer sb = new StringBuffer();
        for (int i = start; i < end; i++) {
            sb.append(s[i]);
        }
        return sb.toString();
    }

    //判断sql和传入语句是否相等
    public boolean equalSplit(int start,int end,String[] s){
        if (split.length!=s.length){
            return false;
        }
        return sp(start,end,s).equals(sp(start,end,split));
    }
    public boolean equalIgSplit(int n,String[] s){
        if (split.length!=s.length){
            return false;
        }
        return sp(n,s).equalsIgnoreCase(sp(n,split))
                &&s.length==split.length;
    }
}







    //分割后字符串拼接
    public String sp(int n,String[] s){
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < n; i++) {
            sb.append(s[i]);
        }
        return sb.toString();
    }

    public String sp(int start,int end,String[] s){
        StringBuffer sb = new StringBuffer();
        for (int i = start; i < end; i++) {
            sb.append(s[i]);
        }
        return sb.toString();
    }

    //判断sql和传入语句是否相等
    public boolean equalSplit(int start,int end,String[] s){
        if (split.length!=s.length){
            return false;
        }
        return sp(start,end,s).equals(sp(start,end,split));
    }
    public boolean equalIgSplit(int n,String[] s){
        if (split.length!=s.length){
            return false;
        }
        return sp(n,s).equalsIgnoreCase(sp(n,split))
                &&s.length==split.length;
    }
}

5.实现服务端类

服务端处理客户端发送过来的sql语句，将相应的信息返回回去

package sqldemo.server;

import sqldemo.domain.Student;
import sqldemo.utils.Judge;

import java.io.*;
import java.net.ServerSocket;
import java.net.Socket;
/*
* 服务器端
* */
public class Server {
    private  ServerSocket ss ;

    public Server(int port) {
        try {
            ss  = new ServerSocket(port);
            System.out.println("服务器正在启动····");
            System.out.println("等待连接");
            init();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void init() throws IOException {
        while (true) {
        //每个客户端打开接受一次连接
            Socket socket = ss.accept();
            System.out.println(socket+"已连接");
            new ServerThread(socket).start();
        }
    }
}


class ServerThread extends Thread {
        private Socket socket;
        private InputStream is;

    public ServerThread(Socket socket){
            this.socket = socket;
        }
       public void run() {
           try {
               is = socket.getInputStream();
               BufferedReader br = new BufferedReader(
                       new InputStreamReader(is));

               PrintWriter pw = new PrintWriter(socket.getOutputStream(), true);
               while (true) {
                       String s = br.readLine();
                       if ("end".equals(s)) {
                           System.out.println(socket.getPort() + "客户端已关闭");
                           pw.close();
                           br.close();
                           socket.close();
                           break;
                       }
                       System.out.println("SQL命令：" + s);
                       //传入sql命令进入方法返回相应的值
                       String msg = sendMsg(s);
                   System.out.println("返回信息：");
                       System.out.println(msg);
                   System.out.println("--------");
                   //为了客户端能读取到多行数据并能正常退出，实现循环输入
                       pw.println(msg+"\r\n"+" ");
               }

               } catch(IOException e){
                   e.printStackTrace();
               }
       }

    public String sendMsg(String sql){
//将sql语句传入judge进行字符串分割
        Judge judge = new Judge(sql);
//分割出表名
        String s = judge.fileName();
            File file = new File("D:\\data\\" + s);
//表名不存在时给出提示，如果语句为添加语句不做判断，没有表直接创建并写入数据
            if (!file.exists()&&s!=null){
                return "没有该表";
     	    }
	if (judge.insert()){
                s=judge.insertFile();
	}

//分割出输入的学生类数据
        String student = judge.stuMem();

            
//将表名和学生类字符串数据传入并进行判断为哪种语句进入实现功能的类
            HandleSql hs = new HandleSql(s,student);
            if (judge.findAll()) {
                return hs.findAll();
            } else if (judge.findOne()) {
                return hs.findOne();
            } else if (judge.delAll()) {
                return hs.delAll();
            } else if (judge.delOne()) {
                return hs.delOne();
            } else if (judge.update()) {
                return hs.update();
            } else if (judge.insert()) {
                return hs.insert();
            }
        return "no no no";
    }
   }

6.编写实现功能前进行处理的类

该类用于查找时拥有缓存操作，语句处理时遇到的问题返回成提示给客户端

package sqldemo.server;

import sqldemo.dao.SqlDao;
import sqldemo.domain.Student;
import sqldemo.utils.FileHandle;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;


public class HandleSql{
    private static Map<Integer, Student> map = new HashMap<Integer, Student>();
    private String fileName;	//输入的表名
    private static String preFileName;//上一次输入的表名
    private String student; //实体类数据字符串
    private SqlDao<Student> fh; //文件处理类

    public HandleSql(String fileName, String student) {
        this.fileName = fileName;
        this.student = student;
        fh = new FileHandle(fileName);

        //不为上次查询的表时，清空map集合重新查找该表并存入map
        if (!fileName.equals(preFileName)){
            preFileName = fileName;
            map.clear();
            List<Student> stu = fh.findAll();
            if (stu!=null){
                for (Student s :stu) {
                    map.put(s.getId(),s);
                }
            }

        }
    }
//判断是否和上一次查询为同一个表


    public String findAll() {
//如果不一样时将为map存入值，然后遍历map集合传出，如果已经存在则直接出来，执行遍历集合传出
        Set<Map.Entry<Integer, Student>> entries = map.entrySet();
        StringBuffer sb = new StringBuffer("id\tname\tage\n");
        for (Map.Entry<Integer,Student> me : entries) {
            Student value = me.getValue();
            sb.append(value.toString()+"\r\n");
        }
        return sb.toString();
    }


    public String findOne() {
        try {
        Set<Map.Entry<Integer, Student>> entries = map.entrySet();
        StringBuffer sb = new StringBuffer("id\tname\tage\n");
        for (Map.Entry<Integer,Student> me : entries) {
            Student stu = fh.findOne(Integer.parseInt(student));
	//查找一个时前面代码规范了语句，传入的只能为id，如果没有返回即无该id，返回提示
            if (stu==null){
                return "输入ID有误";
            }
	//如果和集合中的数据id相等传出该id对应的数据
            if (me.getKey()== stu.getId()) {
                Student value = me.getValue();
                sb.append(value.toString()+"\r\n");
                return sb.toString();
            }
        }
        }catch (NumberFormatException e){
            return "传入ID或age类型错误";
        }
            return "ID输入错误";
    }

    //删除全部，并将表删除
    public String delAll() {
        map.clear();
        return fh.delAll(map);
    }

    //删除对应id的值
    public String delOne() {
        try {
        Set<Map.Entry<Integer, Student>> entries = map.entrySet();
        for (Map.Entry<Integer,Student> me : entries) {
            //找到该id时进入删除语句操作
            if (Integer.parseInt(student)==me.getKey()){
                map.remove(me.getKey());
                return fh.delOne(map);
            }
        }
        }catch (NumberFormatException e){
            return "传入ID或age类型错误";
        }
        return "没有找到对应的ID";
    }
     //修改传入id对应的name数据
    public String update() {
        int count =0;
        try {
            String[] split = student.split(" ");
            Set<Map.Entry<Integer, Student>> entries = map.entrySet();
            for (Map.Entry<Integer, Student> me : entries) {
                if (Integer.parseInt(split[1]) == me.getKey()) {
                    Student value = me.getValue();
                    value.setName(split[0]);
                    count++;
                }
            }
        }catch (NumberFormatException e){
        return "传入ID或age类型错误";
    }
       //拥有该id时进行操作，没有count就为0
        if (count!=0) {
            return fh.update(map);
        }else {
            return "没有对应的ID";
        }
    }

    //增加数据
    public String insert() {
        try {
            String[] split = student.split(",");
            Set<Map.Entry<Integer, Student>> entries = map.entrySet();
            for (Map.Entry<Integer, Student> me : entries) {
                //判断传入的ID是否存在
                if (Integer.parseInt(split[0]) == me.getKey()) {
                    return "该ID已存在";
                }
            }
            //将数据传入map集合，进入文件处理方法
            Student stu1 = new Student();
            stu1.setId(Integer.parseInt(split[0]));
            stu1.setName(split[1]);
            stu1.setAge(Integer.parseInt(split[2]));
            map.put(stu1.getId(), stu1);
            return fh.insert(map);
        }catch (NumberFormatException e){
            return "传入ID或age类型错误";
        }
    }
}

7.规范sql语句实现功能的接口

package sqldemo.dao;

import sqldemo.domain.Student;

import java.util.List;
import java.util.Map;

public interface SqlDao<T> {
    List<T> findAll();
    T findOne(int id);
    String delAll(Map map);
    String delOne(Map map);
    String update(Map map);
    String insert(Map map);
}

8.文件处理类

map集合操作后传入到该类中，用map集合的数据与对应的文件进行操作

package sqldemo.utils;

import sqldemo.dao.SqlDao;
import sqldemo.domain.Student;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class FileHandle implements SqlDao<Student>{
    private String fileName;
    private File file;


    public FileHandle(String fileName) {
        this.fileName = fileName;
        file = new File("D:\\data\\"+fileName);
    }

    @Override
    public List<Student> findAll() {
        BufferedReader br = null;
        ArrayList<Student> list = new ArrayList<>();
        try {
            br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
            String s= null;
            String s1 = br.readLine();
            while ((s=br.readLine())!=null) {
	//从文件中提取出来的数据s	
                String[] split = s.split("\t");
                if ("id".equals(split[0])||("age".equals(split[2]))){
                    continue;
                }
	//将所有对象存入List集合中返回
                Student stu1 = new Student();
                stu1.setId(Integer.parseInt(split[0]));
                stu1.setName(split[1]);
                stu1.setAge(Integer.parseInt(split[2]));
                list.add(stu1);
            }
            br.close();
            return list;
        } catch (FileNotFoundException e) {
            System.out.println("无文件");
            return null;
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("无");
        return null;
    }

    @Override
    public Student findOne(int id) {
        BufferedReader br = null;
        try {
            br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
            String s= null;
            br.readLine();
            while (true) {

                if ((s=br.readLine())==null){
                    br.close();
                    return null;
                }
	//从文件中提取出来的数据s	
                String[] split = s.split("\t");
                if ("id".equals(split[0])||("age".equals(split[2]))){
                    continue;
                }
	//与传入的id一致时返回成实体类对象，前一个处理类已经判断是否拥有该id
                if (id==Integer.parseInt(split[0])){
                    Student stu1 = new Student();
                    stu1.setId(id);
                    stu1.setName(split[1]);
                    stu1.setAge(Integer.parseInt(split[2]));
                    br.close();
                        return stu1;
                }
            }
        } catch (FileNotFoundException e) {
            System.out.println("无文件");
            return null;
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("无");
        return null;
    }

    @Override
    public String delAll(Map map) {
                if (file.delete()){
                    return "删除成功";
                }else {
                    return "删除失败";
                }
    }

    @Override
    public String delOne(Map map) {
            return fileOut(map);
    }

    @Override
    public String update(Map map) {
            return fileOut(map);
    }

    @Override
    public String insert(Map map) {
        return fileOut(map);
    }


    public String fileOut(Map map){
        try {
            File file = new File("D:data");
	//如果没有该文件夹则建立一个文件夹
            if (!file.exists()) {
                file.mkdir();
            }
	//将map集合中的实体类toString方法写入文件
            PrintWriter pw = new PrintWriter(new FileOutputStream("D:\\data\\"+fileName),true);
            Set<Map.Entry<Integer, Student>> entries = map.entrySet();
            StringBuffer sb = new StringBuffer("id\tname\tage\n");
            for (Map.Entry<Integer,Student> me : entries) {
                Student value = me.getValue();
                sb.append(value.toString());
                pw.println(sb.toString());
                sb.append("\n");
            }
            pw.close();
            return "yes ok";
        } catch (FileNotFoundException e) {
            return "文件未找到";
        }
    }
}

客户端和服务端的启动就没展示了，调用构造方法即可