JDBC Insert

public int addPost(PostDO postDO, Integer blogId) {
  java.sql.Timestamp timestamp = getCurrentJavaSqlTimestamp();

  String sql = "INSERT into naukM_" + blogId
      + "_posts (`post_author`, `post_date`,`post_date_gmt`,`post_modified`,`post_modified_gmt`,`post_content`,`post_title`,`post_excerpt`,`to_ping`,`pinged`,`post_content_filtered`,`post_type`,`post_name`,`post_status`) "
      + "values (:post_author, :post_date, :post_date_gmt, :post_modified, :post_modified_gmt, :post_content, :post_title,:post_excerpt,:to_ping, :to_ping,:post_content_filtered,:post_type,:post_name,:post_status)";

  MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
  mapSqlParameterSource.addValue("post_author", postDO.getPostAuthor(), Types.INTEGER);
  mapSqlParameterSource.addValue("post_date", timestamp, Types.DATE);
  mapSqlParameterSource.addValue("post_date_gmt", timestamp, Types.DATE);
  mapSqlParameterSource.addValue("post_modified", timestamp, Types.DATE);
  mapSqlParameterSource
      .addValue("post_modified_gmt", timestamp, Types.DATE);
  mapSqlParameterSource.addValue("post_content", postDO.getPostContent(), Types.VARCHAR);
  mapSqlParameterSource.addValue("post_title", postDO.getPostTitle(), Types.VARCHAR);
  mapSqlParameterSource.addValue("post_excerpt", postDO.getPostExcerpt(), Types.VARCHAR);
  mapSqlParameterSource.addValue("to_ping", postDO.getToPing(), Types.VARCHAR);
  mapSqlParameterSource.addValue("pinged", postDO.getPinged(), Types.VARCHAR);
  mapSqlParameterSource
      .addValue("post_content_filtered", postDO.getPostContentFiltered(), Types.VARCHAR);
  mapSqlParameterSource.addValue("post_type", postDO.getPostType(), Types.VARCHAR);
  mapSqlParameterSource.addValue("post_name", postDO.getPostName(), Types.VARCHAR);
  mapSqlParameterSource.addValue("post_status", postDO.getPostStatus(), Types.VARCHAR);

  KeyHolder keyHolder = new GeneratedKeyHolder();
  jdbcTemplateShard.update(sql, mapSqlParameterSource, keyHolder);
  int id = keyHolder.getKey().intValue();

  postDO.setID(id);
  return id;
}

JDBC Template total count


public Double getTotalPostCount(long blogId, String type, String status) {

Double totalItems;
String q = "SELECT count(*) FROM `naukM_1196_posts` where post_status = '" + status
+ "' and post_type='" + type + "'";
try {
totalItems = jdbcTemplateShard.queryForObject(q, Double.class);
} catch (DataAccessException e) {
LOG.error(e.getMessage());
throw new PersistenceException("Database Error: " + e.getMessage());
}
return totalItems;
}


JDBC template select one with queryForObject

public DetailPostResponse getPostdetails(long blogId, long postId) {
StringBuilder sql = new StringBuilder(“SELECT * FROM naukM_1196_posts “)
.append(” WHERE ID = :ID”);
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue(“ID”, postId);
DetailPostResponse detailPostResponse = new DetailPostResponse();

try {
detailPostResponse = jdbcTemplateShard.queryForObject(sql.toString(), params,
new ResultSetRowToModelMapper(DetailPostResponse.class, true));
} catch (DataAccessException e) {
LOG.error(e.getMessage());
throw new PersistenceException(“Database Error: ” + e.getMessage());
}
return detailPostResponse;
}

JDBC template select all with MapSqlParameterSource

public List getTotalPostList(long blogId, Integer page, Integer perPage,
String type, String status) {
List blogDTOList = new ArrayList();
long start_from = (page – 1) * perPage;

StringBuilder sql = new StringBuilder(
“SELECT p.ID,p.post_date,p.post_author,p.post_mime_type,p.post_type,p.post_content,p.post_title,p.post_status,p.post_name,p.guid,u.user_login “)
.append(“FROM `naukM_” + blogId
+ “_posts` as p join `naukM_users` as u on(p.post_author=u.ID) where “)
.append(” post_status=:post_status and post_type=:post_type order by ID DESC limit “)
.append(start_from + “,” + perPage);

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue(“post_status”, status);
params.addValue(“post_type”, type);

List detailPostResponseList = new ArrayList();
try {
detailPostResponseList = jdbcTemplateShard.query(sql.toString(), params,
new ResultSetRowToModelMapper(DetailPostResponse.class, true));
} catch (DataAccessException e) {
LOG.error(e.getMessage());
throw new PersistenceException(“Database Error: ” + e.getMessage());
}
return detailPostResponseList;
}

JDBC Template Update with prepared statement

public Boolean updateGuidById(long blogId, long postId, String url) {
String SQL =
“UPDATE naukM_” + blogId + “_posts SET guid = ‘” + url + “‘ WHERE ID = ‘” + postId + “‘ “;

try {
jdbcTemplateShard.update(SQL);
} catch (DataAccessException e) {
LOG.error(e.getMessage());
throw new PersistenceException(“Database Error: ” + e.getMessage());
}
return true;
}

JDBC Template Insert with prepared statement


public Integer addNewPost(AddPostRequest addPostRequest) throws SQLException {
long blogId = addPostRequest.getBlogId();
long postAuthor = addPostRequest.getPostAuthor();
String postContent = addPostRequest.getPostContent();
String postTitle = addPostRequest.getPostTitle();
String postName = addPostRequest.getPostName();
String postType = addPostRequest.getPostType();
String postStatus = addPostRequest.getPostStatus();
String image = addPostRequest.getImage();
List category = addPostRequest.getCategory();
List tags = addPostRequest.getTags();
Number lastInsertId = 0;
java.sql.Timestamp timestamp = getCurrentJavaSqlTimestamp();
String nPostName = postName;
try {
// the name of the generated column (you can track more than one column)
String id_column = "ID";
final String INSERT_SQL = " insert into naukM_" + blogId
+ "_posts (`post_author`, `post_date`,`post_date_gmt`,`post_modified`,`post_modified_gmt`,`post_content`,`post_title`,`post_excerpt`,`to_ping`,`pinged`,`post_content_filtered`,`post_type`,`post_name`,`post_status`) values (?,?, ?,?,?,?,?,?,?,?,?,?,?,?) ";
;
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplateShard.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement preparedStatement =
connection.prepareStatement(INSERT_SQL, new String[]{"ID"});
preparedStatement.setLong(1, postAuthor);
preparedStatement.setTimestamp(2, timestamp);
preparedStatement.setTimestamp(3, timestamp);
preparedStatement.setTimestamp(4, timestamp);
preparedStatement.setTimestamp(5, timestamp);
preparedStatement.setString(6, postContent);
preparedStatement.setString(7, postTitle);
preparedStatement.setString(8, "");
preparedStatement.setString(9, "");
preparedStatement.setString(10, "");
preparedStatement.setString(11, "");
preparedStatement.setString(12, postType);
preparedStatement.setString(13, nPostName);
preparedStatement.setString(14, postStatus);
return preparedStatement;
}
},
keyHolder);
lastInsertId = keyHolder.getKey();
} catch (DataAccessException e) {
LOG.error(e.getMessage());
throw new PersistenceException("Database Error: " + e.getMessage());
}
return lastInsertId.intValue();
}


How to get multiple counts with one MYSQL query?


$query = "select
count(*) as total,
sum(case when ContractType = 'renewal' then 1 else 0 end) renewalCount,
sum(case when ContractType = 'new' then 1 else 0 end) newCount
from ".$this->table_name."";


Fetch Call in JavaScript


function ajaxCall(url,options, reqObj,cb) {
let defaultObj = {
method: 'GET',
mode:'cors',
cache:'no-cache',
dataType: "json",
headers:{
'Content-Type': 'application/json',
'Accept': 'application/json'
},
};
let ajaxObj = { ...defaultObj, ...reqObj };
fetch(url, ajaxObj).then(response => {
return cb(response.json());
}).then(data => {
// Work with JSON data here
cb(data);
}).catch(err => {
// Do something for an error here
console.log("Error in getting data..."+err);
});
}


AJax Request


function ajaxCall(url,options, cb) {
jQuery.ajax({
url: url,
type: 'POST',
dataType: "json",
data: {
page: options.currentPage
},
success: function (response) {
cb(response);
},
error: function (err) {
console.log("Error in getting data...");
console.log(err);
}
});
}


Upload image using base64 in php

You can Generate base64 from here:

https://www.base64-image.de/


$img_data=$_POST['image'];
$img_info=explode(',',$img_data);
$image_content=base64_decode($img_info[1]);
$img_extension=substr($img_info[0],11,3);
$img_filename=$_SERVER['DOCUMENT_ROOT'].'/images/img_'.time().'.'.$img_extension;
file_put_contents($img_filename,$image_content);