发表于: 2019-12-01 22:09:07

1 1158



今天做了什么:

必须通过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);

}

收获 

问题 

明天的计划 :

后台-文章管理 调试 

前台-文章管理



返回列表 返回列表
评论

    分享到