发表于: 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);
}
收获
问题
明天的计划 :
后台-文章管理 调试
前台-文章管理
评论