今天做了什么:

必须通过SSH管道才能连接mysql

步骤:

1.导入jsch的jar包

2.创建连接工具类:

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.util.Properties;

public class SSHConnection {

private final static String S_PATH_FILE_PRIVATE_KEY = "/Users/hdwang/.ssh/id_rsa";
private final static String S_PATH_FILE_KNOWN_HOSTS = "/Users/hdwang/.ssh/known_hosts";
private final static String S_PASS_PHRASE = "";
private final static int LOCAl_PORT = 3307;
private final static int REMOTE_PORT = 3306; 
private final static int SSH_REMOTE_PORT = 1022;
private final static String SSH_USER = "zhangsan";
private final static String SSH_PASSWORD = "123456";
private final static String SSH_REMOTE_SERVER = "192.168.0.2";
private final static String MYSQL_REMOTE_SERVER = "123.mysql.com"; 

private Session sesion; //represents each ssh session

public void closeSSH ()
{
    sesion.disconnect();
}

public SSHConnection () throws Throwable
{

    JSch jsch = null;

        jsch = new JSch();
        jsch.setKnownHosts(S_PATH_FILE_KNOWN_HOSTS);
        //jsch.addIdentity(S_PATH_FILE_PRIVATE_KEY);

        sesion = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);

        sesion.setPassword(SSH_PASSWORD);

        Properties config = new Properties();
        config.put("StrictHostKeyChecking", "no");
        sesion.setConfig(config);

        sesion.connect(); //ssh connection established!

        //by security policy, you must connect through a fowarded port          
        sesion.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);

}
}

3.管理ssh连接:

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

@WebListener
public class MyContextListener implements ServletContextListener {

    private SSHConnection conexionssh;


    public MyContextListener() {
        super();
    }

    /**
     * @see ServletContextListener#contextInitialized(ServletContextEvent)
     */
    public void contextInitialized(ServletContextEvent arg0) {
        System.out.println("Context initialized ... !");
        try {
            conexionssh = new SSHConnection();
        } catch (Throwable e) {
            e.printStackTrace(); // error connecting SSH server
        }
    }

    /**
     * @see ServletContextListener#contextDestroyed(ServletContextEvent)
     */
    public void contextDestroyed(ServletContextEvent arg0) {
        System.out.println("Context destroyed ... !");
        conexionssh.closeSSH(); // disconnect
    }

}

后台-文章管理部分持久层:

@Mapper
public interface bArticleMapper {
    //所有文章
    @Select("select * from article")
    ArrayList<Article> getAllArticles();
    //点赞
    @Update("update article set number_of_likes=number_of_likes+1 where id = #{aid}")
    boolean putArticleLike(Long aid);
    //收藏数+1
    @Update("update article set number_of_collections=number_of_collections+1 where id = #{aid}")
    boolean putArticleCollection(Long aid);
    //关系表中记录
    @Insert("insert into article_collection (sid,aid) values (#{sid},#{aid})")
    boolean collectArticle(Long sid,Long aid);
    //文章详情
    @Select("select * from article where id = #{id}")
    Article getArticle(Long id);
    //文章新增
    @Insert("insert into article (title,type,image,author,introduction,content) " +
            "valuse (#{title},#{type},#{image},#{author},#{introduction},#{content})")
    boolean insertArticle(Article article);
    //文章编辑
    @Update("update article set title=#{title},type=#{type},image=#{image},author=#{author},introduction=#{introduction},content=#{content}" +
            "where id = #{id}")
    boolean updateArticle(Article article);
    //删除
    @Delete("delete article where id = #{id}")
    boolean deleteArticle(Long id);
    //上下架
    @Update("update article set status = #{stauts} where id = #{id}")
    boolean updateArticleStatus(Long id,int status);
    //动态查询
    @Select("select * from article where \n" +
            "\n" +
            "<if test=\"id!=null\">\n" +
            "\n" +
            "id=#{id}\t</if> \n" +
            "\n" +
            "<if test=\"status!=null\"> \n" +
            "\n" +
            "status=#{status}\t</if>\n" +
            "\n" +
            " <if test=\"type!=null\">\n" +
            "\n" +
            "type=#{type}\t</if> \n" +
            "\n" +
            "<if test=\"author!=null\">  \n" +
            "\n" +
            "author=#{author}\t</if> \n" +
            "\n" +
            "<if test=\"likes_min!=null\"> \n" +
            "\n" +
            "number_of_likes > #{likes_min}</if> \n" +
            "\n" +
            "<if test=\"likes_max!=null\"> \n" +
            "\n" +
            "number_of_likes < #{likes_max}</if> \n" +
            "\n" +
            "<if test=\"collections_min!=null\"> \n" +
            "\n" +
            "number_of_collections > #{collections_min}</if>\n" +
            "\n" +
            " <if test=\"collections_max!=null\"> \n" +
            "\n" +
            "number_of_collections < #{collections_max}</if>\n" +
            "\n"
    )
    ArrayList<Article> getArticles(Article article);

}

收获 

问题 

明天的计划 :

后台-文章管理 调试 

前台-文章管理