SQL

Structured Query Language

Date

SELECT * from dialogue D
    inner join (
        SELECT TIME_TO_SEC(TIMEDIFF(end_date, begin_date)) seconds
        from dialogue
    ) S on S.seconds >= 0 and S.seconds <= 100
where timeout_count >= 1;

DDL

Data Definition Language

• create

  • create table tb_name;
  • create table tb_name(column_enum enum('x','y') CHARACTER NOT NULL DEFAULT 'x');

• show

  • show create table tb_name; // show the SQL of creating table

• drop

  • drop table tb_name;

• alter

  • alter table tb_name rename to tb_new_name;
  • alter table tb_name add column_name column_type;
  • alter table tb_name modify column_name column_new_type;
  • alter table tb_name change column_name column_new_name column_new_type;
  • alter table tb_name drop column_name;

DML

Data Manipulation Language

• insert

  • insert into tb_name(column_name1, column_name2) values(?, ?);
  • insert into tb_name(column_name1, column_name2) values(column_name1_value, column_name2_value);
  • insert into tb_name values(column_name1_value, column_name2_value);
  • insert into tb_name(column_name1, column_name2) values(column_name1_value, column_name2_value), (column_name1_value2, column_name2_value2);

• update

  • update tb_name set column_name1=column_value, column_name2=column_value where conditions;

• delete

  • delete from tb_name where conditions;

DQL

Data Query Language

• select

  • select * from tb_name;
  • select column_name1, column_name2 from tb_name;
  • select column_name1 c1_alias, column_name2 c2_alias from tb_name;
  • select distinct column_name from tb_name;
  • select column_name1 as column_alias_name1, column_name2 column_alias_name2 from tb_name;

• where

  • >=
  • <=
  • =
  • != / <>
  • && / and
  • between x and y
  • || / or / in (x,y,z)
  • is / not null
  • like 'x%', '_x%', '%x%'

• order by

  • select * from tb_name order by column_name asc/desc, column_name2 asc/desc;

• group by

  • select column_name1, avg(column_name2) group by column_name1;
  • select from where group by having count(*) > 2

the column name must be not null, should be primary key or *.

• functions

  • count(column_name)
  • max
  • min
  • avg
  • sum

• limit (MySQL)

  • select * from tb_name limit page_index(page_no * page_size), page_size;

• rownumber (Oracle)

• top (SQL Server)

DCL

Data Control Language (Permissions control)

Column Constraint

• unique
  • auto_increment (number)
• not null
• primary key
• check
• foreign key
  • constraint fk_tb1_tb2 foreign key(tb2_id) references tb2(id)
  • alter table tb1 drop foreign key(fk_tb1_tb2)
  • alter table tb1 add constraint fk_tb1_tb2 foreign key(tb2_id) references tb2(id)
• default

Database Design

Query Among Tables

• select * from tb1,tb2 where tb1.tb2_id = tb2.id;
• select tb1.column1, tb2.column1 from tb1,tb2 where tb1.tb2_id = tb2.id;
• select t1.column1, t2.* from tb1 t1,tb2 t2 where t1.tb2_id = t2.id; # (table alias)
• select * from tb1 /inner/ join tb2 on tb1.tb2_id = tb2.id inner join xx on xx;
• select * from tb1 left /outer/ join tb2 on tb1.tb2_id = tb2.id;
• select * from tb1 right /outer/ join tb2 on tb1.tb2_id = tb2.id;

Subquery

• select * from tb1 where column1 [>,in] (select column1 from tb1 where conditions);
• select * from (select * from tb1 where conditions) t1, tb2 where t1.tb2_id = tb2.id; # virtual table

select * from tb2,(select tb2_id, count(*) count from tb1 group by tb2_id) t1 where tb2.id = t1.tb2_id;

Transaction

• start transaction / begin

• commitc // persistent

• rollback

• set @@autocommit 0; // MySQL default is 1

• Characteristics:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

JDBC

Java Da ta Base Connectivity
Steps:

• Register driver class through Class

• Get a Connection object through DriverManager

  • DriverManager
    • registerDriver(new Driver())
      optional after version 5.0
    • getConnection(url, username, password)
      url: jdbc:mysql://host:port/db_name?useSSL=false
  • Connection
    • createStatement()
    • prepareStatement(sql)
    • prepareCall(sql)
    • setAutocommit(boolean)
    • commit()
    • rollback()
  • Statement
    • int count = executeUpdate(sql)
      success with > 0
    • ResultSet set = executeQuery(sql)
  • PreparedStatement
    • &useServerPrepStmts=true

• Provide a SQL string

• Create a SQL Statement object

• Execute SQL string through statement object

• Close statement

• Close connection

Connection Pool

• DataSource

1. Properties prop = new Properties();
prop.load(new FileInputStream('druid.properties'));
2. DataSource ds = DruidDataSourceFactory.createDataSource(Properties prop);
3. Connection conn = ds.getConnection();

• DBCP
• C3P0
• Druid(Alibaba) # recommend

1. import druid.x.jar
2. config druid.properties file:
   driverClassName=xx
   url=
   username=
   password=
   initialSize=5
   maxActive=10
   maxWait=

Demo Codes

import com.demo.pojo.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class App {
    public static void main(String[] args) throws Exception {
        System.out.println("hello java");

        try {
            Connection connection = connectDefaultDB();
            if(connection == null) {
                System.out.println("Default database `db` is connected failed!");
                return;
            }
            List<User> users = selectAllUsers(connection);
            if (users.isEmpty()) {
                System.out.println("Inserting users success: " + insertUsers(connection));
                users = selectAllUsers(connection);
            }
            System.out.println("Queried users: "+users);
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 连接默认数据库 `db`
     * @return Connection
     * @throws SQLException
     */
    private static Connection connectDefaultDB() throws SQLException {
        return connectDB("db", "root", "xxx");
    }

    /**
     * 连接 localhost/mysql 数据库
     * @param dbName String
     * @param userName String
     * @param password String
     * @return Connection
     * @throws SQLException
     */
    private static Connection connectDB(String dbName, String userName, String password) throws SQLException {
//        Class.forName("com.mysql.cj.jdbc.Driver");
        String dbUrl = "jdbc:mysql:///" + dbName + "?useSSL=false";
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(dbUrl, userName, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return connection;
    }

    /**
     * 插入2条用户信息
     * @param connection Connection
     * @return boolean success
     * @throws SQLException
     */
    private static boolean insertUsers(Connection connection) throws SQLException {
        String sql = "insert into user(name,phone,age,sex) " +
                "values('user1', '18800000000', 24, 1)," +
                "('user2', '18900000000', 34, 0)";
        PreparedStatement ps = connection.prepareStatement(sql);
        int executed = ps.executeUpdate();
        ps.close();
        return executed > 0;
    }

    /**
     * 查询所有用户
     * @param connection Connection
     * @return List<User>
     * @throws SQLException
     */
    private static List<User> selectAllUsers(Connection connection) throws SQLException {
        List<User> list = new ArrayList<>();
        try {
            String sql = "select * from user";
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet set = ps.executeQuery(sql);
            while (set.next()) {
                User user = new User();
                user.setId(set.getInt("id"));
                user.setName(set.getString("name"));
                user.setPhone(set.getString("phone"));
                user.setAge(set.getInt("age"));
                user.setSex(set.getInt("sex"));
                list.add(user);
            }
            set.close();
            ps.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
}

Maven(Apache)

Provide a standard project structure.
Others: Gradle, Ant

• Compile
• Test
• Package
• Deploy

pom.xml
main/
test/

Repository

• Local(Cache)
• Central(Public)
• Remote(Mirror/Private)

Command

• compile
  mkdir target/*.class

• package
  target/xx.jar

• install
  add to local repository

• clean
  rm -rf target/

• test

• validate

• deploy

Lifecycle

install > package > test > compile

Coordinate

• <groupId>
• <artifactId>
• <version>
• <scope> compile/test/provided/runtime/system/import

MyBatis

Simplify the JDBC coding procedure.

try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = sqlSessionFactory.openSession();
            List<User> users = session.selectList("com.demo.user.Mapper.selectAll");
            System.out.println(users);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(101);

Servlet

URL Encode & Decode

以下方式适用于tomcat 7及以上，8+已自动解决该问题。

// 模拟浏览器URL编码
        String oriParam = "张三";
        String encoded = URLEncoder.encode(string, "utf-8");
        String mockParams = URLDecoder.decode(encoded, "iso-8859-1"); //StandardCharsets.ISO_8859_1
        
        // URL中文乱码解码
        byte[] bytes = mockParams.getBytes("iso-8859-1");
        String   = new String(bytes, "utf-8");
 // default charset-name is "utf-8"
        System.out.println(decoded);

HTTPRequest forward

req.getRequestDispatcher("uri").forward(req, resp);
req.setAttribute(String name, value);
req.getAttribute(String name);
req.removeAttribute(String name);

HTTPResponse

resp.setStatus(int code)
resp.setHeader(name, value)

// output chars/string
resp.setContentType("text/html;charset=utf-8")
resp.getWriter().write(String value)

// output bytes
new FileInputStream(url) //maven commons-io
resp.getOutputStream().write(byte[], 0, length)

Redirect
resp.sendRedirect(req.getContextPath()+relativeUri)

• status code: 302
• header: Location:uri

JSP

Java Server Pages
= HTML + Java
-> Servlet

<%
System.out.println("xxx")
%>
<%= value %>
<% ! global value/function %>

EL

Expression Language

Servlet.forward(.jsp)

${data}

page <- request <- session <- application

JSTL

Jsp Standarded Tag Library

<% @ taglib prefix="c" uri="jstl/core" %>


<c:if test="${flag == 1}">
xxx
</c:if>
<c:forEach></c:forEach> // forEach() / for(int i)

Project Structure

SSM
MVC

1. com.xx.web/controller
   SpringMVC/Structs2

• Controller
  • Servlet
  • JSP

2. com.xx.service
   Spring

• Service
  • Mapper
  • DAO

3. com.xx.dao/mapper
   Mybatis/Hibernate

• DAO
  • Mapper
  • DAO

Cookie

• Max size with 30K.
• Not support with Chinese characters.
• Stored in browser's memory with default configuration.
• But you can set a cookie's age by Cookie.setMaxAge(int seconds) to persist the cookie data with the time of age seconds:
  • >0 persist with seconds
  • <0 will remove when browser is closed
  • =0 will remove immediately
• Get cookie value in jsp page: ${cookie.xxx.value}.

Get cookie from a http request

Cookie[] list = req.getCookies(key)
cookie.getName()
cookie.getValue()

Send a cookie to browser

resp.addCookie(new Cookie(key, value))

Response with http header:
Set-C ookie:name=value

Request with http header:
Cookie:name=value; name2=value2

Session

Implemented with Cookie:

• Set-Cookie: JSESSIONID=xxx
• Cookie: JSESSIONID=xxx

Session store & reload & destroy

• Server will persist the session data into disk (tomcat/SESSIONS.ser(ialized)) when the server shutting down.
• Will reload session data when the server started again.
• Will destroy data with <session-config-timeout>minutes(default is 30). or use session.invalidate()

HttpSession session = req.getSession()
session.setAttribute(name, value)
session.getAttribute(name)
session.removeAttribute(name)

Filter

• @WebFilter("index.html")
• @WebFilter("/*.jsp")
• @WebFilter("/user/")
• @WebFilter("/*")

Filters will be executed one by one with the order of the filter class's name.

Needs check the request.getRequestURL().toString() contains the ignored uris or not.

@WebFilter("/*")
public class FilterDemo implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
        Filter.super.init(filterConfig);
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        // handle request
        // then handle others with `chain.doFilter(req, resp)`

        // callback after handled the chain filter
    }

    @Override
    public void destroy() {
        Filter.super.destroy();
    }

Listener

Listening these objects are initialized, destroyed, the attributes are
changed:

• application
• session
• request

@WebListener
public class ContextListenerDemo implements ServletContextListener {
    @Override
    public void contextInitialized(ServletContextEvent sce) {
        // load resources
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        // release resources
    }
}

AJAX

Asynchronous Javascript And XML

axios

JSON

Java Script Object Notation

fastjson

Vue

https://cn.vuejs.org/

MVVM <-> MVC

Epxressions

• v-bind
• v-model
• v-on
  • v-on:click="xxx()"
  • @click="xxx()"
• v-for="(item, i) in items"
• v-if
• v-else if
• v-else
• v-show

Lifecyles

• beforeCreate
• created
• beforeMount
• mounted
  • Vue initialized
  • HTML rendered
• beforeUpdate
• updated
• beforeDestroy
• destroyed

Element

The most popular Vue UI framework.

https://element.eleme.cn/#/zh-CN

var _this = this;

Tomcat

public void run() {
        try {
            ServerSocket socket = new ServerSocket(8080);
            while (!socket.isClosed()) {
                Socket accept = socket.accept();
 
                LxbRequestHandle requestHandle = new LxbRequestHandle(accept);
 
                new Thread(requestHandle).start();
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
 
// Class.forName(servletClass.getText().trim()).newInstance()

Request

public class LxbHttpRequest {
    private Socket socket;
    private String method;
    private String uri;
    private HashMap<String, String> parameterMapping = new HashMap<String, String>();
 
    public LxbHttpRequest(Socket socket) {
        this.socket = socket;
        try {
            ParseProcess();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
 
    public void ParseProcess() throws Exception {
        BufferedReader reader =
                new BufferedReader(new InputStreamReader(socket.getInputStream(), "utf-8"));
 
        String temp;
 
        temp = reader.readLine();
 
//        GET /cal.html HTTP/1.1
        String[] request = temp.split(" ");
        method = request[0];
        int index = request[1].indexOf("?");
 
        if (index == -1) {
            uri = request[1];
        } else {
            uri = request[1].substring(0, index);
 
            String parameterPair = request[1].substring(index + 1);
            String[] parameters = parameterPair.split("&");
 
            if (parameters[0] != null && !parameters[0].equals("")) {
                for (String parameter : parameters) {
                    String[] split = parameter.split("=");
 
                    if(split.length == 2) {
                        parameterMapping.put(split[0], split[1]);
                    }
                }
            }
        }
    }
 
    public String getMethod() {
        return method;
    }
 
    public String getUri() {
        return uri;
    }
 
    public String getParameter(String key) {
        if(parameterMapping.get(key) != null) {
            return parameterMapping.get(key);
        } else {
            return "";
        }
    }
 
    @Override
    public String toString() {
        return "LxbHttpRequest" +
                "{ method = " + method +
                ", uri = " +  uri +
                ", parameter = " + parameterMapping +
                "}";
    }
}

RequestHandle

public class LxbRequestHandle implements Runnable{
    private Socket accept;
 
    public LxbRequestHandle(Socket socket) {
        this.accept = socket;
    }
 
    public void run() {
        try {
            LxbHttpRequest request = new LxbHttpRequest(accept);
            LxbHttpResponse response = new LxbHttpResponse(accept.getOutputStream());
 
            String uri = request.getUri();
            if(WebUtils.isHtml(uri)) {
                System.out.println(uri.substring(1));
                String resp = WebUtils.readFile(uri.substring(1));
                String responseInfo = LxbHttpResponse.respHeader + resp;
 
                OutputStream outputStream = response.getOutputStream();
                outputStream.write(responseInfo.getBytes());
 
                outputStream.flush();
                outputStream.close();
                accept.close();
                return;
            }
 
 
            String servletName = LxbTomcatV3.servletMapping.get(uri);
            if(servletName == null) {
                servletName = "";
            }
 
            LxbHttpServlet servlet = LxbTomcatV3.servlet.get(servletName);
 
            if(servlet != null) {
                servlet.service(request, response);
            } else {
                String resp = LxbHttpResponse.respHeader + "<h1>404 not found!!!</h>";
                OutputStream responseOutputStream = response.getOutputStream();
                responseOutputStream.write(resp.getBytes());
                responseOutputStream.flush();
                responseOutputStream.close();
            }
 
            accept.close();
 
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (accept != null) {
                try {
                    accept.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Custom Servlet

String uri = req.getRequestURI().toString();//"/demo/getAllUsers";
        String[] strings = uri.split("\\/");
        String methodName = strings.last();//[strings.length-1];
        System.out.println(methodName);
        Class<? extends DemoServlet> aClass = this.getClass();
        try {
            Method method = aClass.getMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
            method.invoke(this, req, resp);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }