Spark framework to mysql

来源：互联网发布：为什么一淘比淘宝便宜编辑：程序博客网时间：2024/06/08 00:27

官网，建立一个小型的framework去连接数据库sql2o

在此处我讲讲解怎么用spark framework这个小型的微框架（麻雀虽小，五脏俱全，这个框架能完成网页全部最基本的操作，但是它的所占的空间是是很小的）连接到本机的mysql上，是对官方代码的修改，同时对官网的设计方法做出个人的分析。orm（对象关系映射）是一种设计方法，说的是怎么在对象和数据库中数据的存储之间建立一套完整的操作机制。一句话概括O R M 技术是在对象和关系之间提供了一条桥梁，前台的对象型数据和数据库中的关系型的数据通过这个桥梁来相互转。首先是依赖关系，即用maven打包必须需要用到的pom.xml文件，用来下载相关的依赖，使得在程序中调用到的类能正常载入使用。

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">    <modelVersion>4.0.0</modelVersion>    <groupId>me.tomassetti</groupId>    <artifactId>BlogService</artifactId>    <packaging>jar</packaging>    <version>1.0-SNAPSHOT</version>    <name>BlogService</name>    <url>http://maven.apache.org</url>    <properties>        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>    </properties>    <dependencies>        <dependency>            <groupId>junit</groupId>            <artifactId>junit</artifactId>            <version>4.12</version>            <scope>test</scope>        </dependency>        <dependency>            <groupId>com.sparkjava</groupId>            <artifactId>spark-core</artifactId>            <version>2.3</version>        </dependency>        <dependency>            <groupId>com.fasterxml.jackson.core</groupId>            <artifactId>jackson-core</artifactId>            <version>2.6.4</version>        </dependency>        <dependency>            <groupId>com.fasterxml.jackson.core</groupId>            <artifactId>jackson-databind</artifactId>            <version>2.6.4</version>        </dependency>        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>            <version>1.16.6</version>            <scope>provided</scope>        </dependency>        <dependency>            <groupId>org.sql2o</groupId>            <artifactId>sql2o</artifactId>            <version>1.5.4</version>        </dependency>        <dependency>            <groupId>org.postgresql</groupId>            <artifactId>postgresql</artifactId>            <version>9.4-1206-jdbc42</version>        </dependency>        <dependency>            <groupId>com.google.guava</groupId>            <artifactId>guava</artifactId>            <version>19.0</version>        </dependency>        <dependency>            <groupId>org.easymock</groupId>            <artifactId>easymock</artifactId>            <version>3.4</version>            <scope>test</scope>        </dependency>        <dependency>            <groupId>com.google.code.gson</groupId>            <artifactId>gson</artifactId>            <version>2.5</version>            <scope>test</scope>        </dependency>        <dependency>            <groupId>com.beust</groupId>            <artifactId>jcommander</artifactId>            <version>1.48</version>        </dependency>        <dependency>            <groupId>com.sparkjava</groupId>            <artifactId>spark-template-freemarker</artifactId>            <version>2.3</version>        </dependency>        <dependency>            <groupId>com.j2html</groupId>            <artifactId>j2html</artifactId>            <version>0.7</version>        </dependency>    </dependencies>    <build>        <plugins>            <plugin>                <groupId>org.apache.maven.plugins</groupId>                <artifactId>maven-compiler-plugin</artifactId>                <version>3.2</version>                <configuration>                    <source>1.8</source>                    <target>1.8</target>                    <encoding>${project.build.sourceEncoding}</encoding>                </configuration>            </plugin>            <plugin>                <groupId>org.codehaus.mojo</groupId>                <artifactId>exec-maven-plugin</artifactId>                <version>1.2.1</version>                <configuration>                    <mainClass>me.tomassetti.BlogService</mainClass>                    <arguments>                    </arguments>                </configuration>            </plugin>        </plugins>    </build></project>

最后的build内容是打包类建立文件时必须加入的，如果缺少，则Maven Compile插件默认会加-source 1.5以及target -1.5来参数编译（估计是为了兼容一些老的Linux服务器操作系统，它们通常只有JDK 5），而由于我们的代码里用了JDK 7/8的语法，因此需用更改默认的属性。，需要加入在properties里面加入代码

<properties>      <maven.compiler.source>1.8</maven.compiler.source>      <maven.compiler.target>1.8</maven.compiler.target>    </properties>

前期准备：
需要在本机的数据库中建立名字为blog数据库（当然可以自己变），
然后新建相关的表；

create database blog;create table posts(post_uuid char(50) primary key,title text not null, content text,publishing_data date);create table comments(comment_uuid char(50) primary key,post_uuid char(50) posts(post_uuid),author text,content text,approved bool,submission_data date);create table post_categories(post_uuid char(50) references post(post_uuid),category text);

此处的UUID我用的是char(50)，来存，因为mysql中并没有UUID类型，而我开始的时候也考虑了，如果我在对象中用的是String类型，那么存取的时候是否能匹配UUID一致？经过实验后答案是肯定的，我们可以用String类型来匹配mysql中的char（）和text类型。

代码的介绍说明

数据库的连接

官网的连接方法

private static final Logger logger = Logger.getLogger(BlogService.class.getCanonicalName());    public static void main( String[] args) {        CommandLineOptions options = new CommandLineOptions();        //这里把连接数据库用到的信息集成到一个类中，可以方便下次调用使用，也是一种简化代码的方式        new JCommander(options, args);//JCommander事JsonDe一个类，用来串联命令行和数据，可以在命令行中匹配登陆连接数据库        //也只是猜测相应的功能      //以下是为logger类加入信息        logger.finest("Options.debug = " + options.debug);        logger.finest("Options.database = " + options.database);        logger.finest("Options.dbHost = " + options.dbHost);        logger.finest("Options.dbUsername = " + options.dbUsername);        logger.finest("Options.dbPort = " + options.dbPort);        logger.finest("Options.servicePort = " + options.servicePort);        port(options.servicePort);//链接接口，此处还是不是很理解        Sql2o sql2o = new Sql2o("jdbc:mysql://" + options.dbHost + ":" + options.dbPort + "/" + options.database,                options.dbUsername, options.dbPassword, new PostgresQuirks() {            {                // make sure we use default UUID converter.                converters.put(UUID.class, new UUIDConverter());            }        });//连接数据库，并新建一个连接的对象，或者可以称之为接口        Model model = new Sql2oModel(sql2o);//为Sql2oModel传入数据库接口

这是将登陆的信息集成到一个类中去掉用。通过sql2o，新建一个sql2o对象,然后传入主机名，主机的连接数据库的端口，数据库名，数据库的用户名，用户密码，以及 PostgreSQL 的对象-关系型数据库管理系统，来确保连接。

其实如果只是单纯的连接，不需要不需要系统的安全等要求可以直接输入这几个元素来直接连接，例如：

Sql2o sql2o =new Sql2o("jdbc:mysql://localhost:3306/blog","root","xz");//连接到我的mysql

建立一个对像Sql2oModel来对数据库进行操作

先建立具体的信息对象

public class Comment {    String comment_uuid;    String post_uuid;    String author;    String content;    boolean approved;    Date submission_date;}public class Post {    private String post_uuid;    private String title;    private String content;    private Date publishing_date;    private List<String> categories;    public String getPost_uuid(){return post_uuid;}      public String getContent(){return content;}      public String getTitle(){return title;}      public void setCategories(List<String>cate){categories=cate;}}

然后定义一个Sql2oModel的接口，具备改变数据库的完备功能

public interface Model {    String createPost(String title, String content, List<String> categories);//创建Post对象并插入到数据中    String createComment(String post, String author, String content);//创建Comment对象并与Post关联    List<Post> getAllPosts();//获取所有的Posts对象，存在链表中    List<Comment> getAllCommentsOn(String post);//获取所有的Comments对象，返回链表类    boolean existPost(String post);//根据Posts来判断是否存在    Optional<Post> getPost(String uuid);//根据uuid来判断对象是否存在    void updatePost(Post post);//来更新信息    void deletePost(String uuid);//删除掉Posts对象}

接下来是Sql2oModel的实现

public class Sql2oModel implements Model{  private Sql2o sql2o;//私有化      private UuidGenerator uuidGenerator;  public Sql2oModel(Sql2o sql2o) {//用sql2o做初始化，在Sql2oMdel的内部来执行对数据库的操作        this.sql2o = sql2o;        uuidGenerator= new RandomUuidGenerator(); //获取一个随机的uuid    }    @Override    public String createPost(String title, String content, List<String> categories) {//创建一个post对象，插入到数据库中        try (Connection conn = sql2o.beginTransaction()) {//开始对数据库的操作            String postUuid = uuidGenerator.generate().toString();            conn.createQuery("insert into posts(post_uuid, title, content, publishing_date) VALUES (:post_uuid, :title, :content, :date)")                    .addParameter("post_uuid", postUuid)//由于mysql里面没有uuid类型，所以需要全部把，uuid类型转换为String类型在数据库中的表现为text类型                    .addParameter("title", title)                    .addParameter("content", content)                    .addParameter("date", new Date())                    .executeUpdate();//加入进去后更新数据            categories.forEach((category) ->                    conn.createQuery("insert into posts_categories(post_uuid, category) VALUES (:post_uuid, :category)")                    .addParameter("post_uuid", postUuid)                    .addParameter("category", category)                    .executeUpdate());            conn.commit();            return postUuid;        }    }    @Override    public String createComment(String post, String author, String content) {        try (Connection conn = sql2o.open()) {            String commentUuid = uuidGenerator.generate().toString();            conn.createQuery("insert into comments(comment_uuid, post_uuid, author, content, approved, submission_date) VALUES (:comment_uuid, :post_uuid, :author, :content, :approved, :date)")                    .addParameter("comment_uuid", commentUuid.toString())                    .addParameter("post_uuid", post)                    .addParameter("author", author)                    .addParameter("content", content)                    .addParameter("approved", false)                    .addParameter("date", new Date())                    .executeUpdate();            return commentUuid;        }    }    @Override    public List<Post> getAllPosts() { //获取所有的对象        try (Connection conn = sql2o.open()) {            List<Post> posts = conn.createQuery("select * from posts")                    .executeAndFetch(Post.class);//执行指令            posts.forEach((post) -> post.setCategories(getCategoriesFor(conn, post.getPost_uuid())));            return posts;        }    }    private List<String> getCategoriesFor(Connection conn, String post_uuid) {//来根据UUID来查找对象        return conn.createQuery("select category from posts_categories where post_uuid=:post_uuid")                .addParameter("post_uuid", post_uuid.toString())                .executeAndFetch(String.class);    }    @Override    public List<Comment> getAllCommentsOn(String post) {        try (Connection conn = sql2o.open()) {            return conn.createQuery("select * from comments where post_uuid=:post_uuid")                    .addParameter("post_uuid", post.toString())                    .executeAndFetch(Comment.class);        }    }    @Override    public boolean existPost(String post) {        try (Connection conn = sql2o.open()) {            List<Post> posts = conn.createQuery("select * from posts where post_uuid=:post")                    .addParameter("post", post.toString())                    .executeAndFetch(Post.class);            return posts.size() > 0;        }    }  @Override    public Optional<Post> getPost(String uuid) {        try (Connection conn = sql2o.open()) {            List<Post> posts = conn.createQuery("select * from posts where post_uuid=:post_uuid")                    .addParameter("post_uuid", uuid)                    .executeAndFetch(Post.class);            if (posts.size() == 0) {                return Optional.empty();            } else if (posts.size() == 1) {                return Optional.of(posts.get(0));            } else {                throw new RuntimeException();            }        }    }    @Override    public void updatePost(Post post) {        try (Connection conn = sql2o.open()) {            conn.createQuery("update posts set title=:title, content=:content where post_uuid=:post_uuid")                    .addParameter("post_uuid", post.getPost_uuid())                    .addParameter("title", post.getTitle())                    .addParameter("content", post.getContent())                    .executeUpdate();        }    }    @Override    public void deletePost(String uuid) {        try (Connection conn = sql2o.open()) {            conn.createQuery("delete from posts where post_uuid=:post_uuid")                    .addParameter("post_uuid", uuid)                    .executeUpdate();        }    }}

这个是orm设计思想的体现，集成了所有对数据库的接口操作，用creatQuery（String ）来传入命令，更新数据，完成增删改查的操作。

接下来是在main函数中，建立路由的映射机制，可以通过访问网站来执行映射的相应操作

post("/posts", (request, response) -> {//创建相应的post对象         ObjectMapper mapper = new ObjectMapper();  //用一个map的容器来实现映射         NewPostPayload creation = mapper.readValue(request.body(), NewPostPayload.class);//把request的整体框架与一个Post         if (!creation.isValid()) {//判断是否存在。             response.status(HTTP_BAD_REQUEST);//如果错误就返回错误的状态值             return "";         }         String id =new String( model.createPost(creation.getTitle(), creation.getContent(), creation.getCategories()));        /*UUID id = UUID.fromString(model.createPost("creation.getTitle()", "creation.getContent()", new LinkedList<>()));*/        StringWriter sw = new StringWriter();//此处不直接返回String类型，是我尝试直接返回是有错误的，所以直接参考了datetoJson的方法来返回，我觉得是数据需要封装后才能争取读到返回的信息。        mapper.writeValue(sw, id);         response.status(200);         response.type("application/json");         return sw.toString();//返回特定的uuid     });     // get all post (using HTTP get method)     get("/posts", (request, response) -> {//获取所有post对象的信息         response.status(200);         response.type("application/json");         return dataToJson(model.getAllPosts());     });     post("/posts/:uuid/comments", (request, response) -> {//创建一个comment对象并与相应的post联系         ObjectMapper mapper = new ObjectMapper();         NewCommentPayload creation = mapper.readValue(request.body(), NewCommentPayload.class);         if (!creation.isValid()) {             response.status(HTTP_BAD_REQUEST);             return "";         }         if (!model.existPost(request.params(":uuid"))){             response.status(400);             return "";         }         UUID id = UUID.fromString(model.createComment(request.params(":uuid"), creation.getAuthor(), creation.getContent()));         StringWriter sw = new StringWriter();         mapper.writeValue(sw, id);         response.status(200);         response.type("application/json");         return sw.toString();//返回uuid     });     get("/posts/:uuid/comments", (request, response) -> {//得到相应uuid的comment对象         if (!model.existPost(request.params(":uuid"))) {             response.status(400);             return "";         }         response.status(200);         response.type("application/json");         return dataToJson(model.getAllCommentsOn(request.params(":uuid")));     });    }